ALTER TABLE patch

IssueTrackerProduct

About my baby Open Source project

Kung Fu

Fujian White Crane Kung Fu

Zope

What I have and am doing with Zope

Photos

Photoalbum, both old and new.

Receptsamlingen

In Swedish only. About my "Collection of Recipes" website.

Contact me

My contact details and how to contact me.

Mobile version of this page

Save this page in del.icio.us

Previous:
iWipe - toilet paper and a Mac
Next:
Geek entrepreneurs' reading list

Related by category

Work

postgresql, alters, alter, oracle, mysql, alter table, null

11^th of December 2005

Since I always forget how to do this and have to reside to testing back and forth or to open a book I will now post an example patch that alters a table; correctly. Perhaps this is done differently in Oracle, MySQL, etc. but in PostgreSQL 7.4 you can't add a new column and set its default and constraints in the same command. You have to do that separately for it to work and most likely you'll have to run a normal update once the column has been added.

Just doing:

ALTER TABLE users ADD mobile VARCHAR(20) NOT NULL DEFAULT '';

...won't work and you'll get an error.

Suppose for example that you created a table like this:

CREATE TABLE users (
"uid"                  SERIAL PRIMARY KEY,
"password"             VARCHAR(20) NOT NULL,
"email"                VARCHAR(100) NOT NULL DEFAULT ''
);

But what you really want is this:

CREATE TABLE users (
"uid"                  SERIAL PRIMARY KEY,
"password"             VARCHAR(20) NOT NULL,
"email"                VARCHAR(100) NOT NULL DEFAULT '',
"mobile"               VARCHAR(20) NOT NULL DEFAULT ''
);

Either you can drop the table and all its content and start again or else you can do the following:

BEGIN;

ALTER TABLE users
  ADD mobile VARCHAR(20);

ALTER TABLE users
  ALTER mobile SET DEFAULT '';

UPDATE users
  SET mobile = '' WHERE mobile IS NULL;

ALTER TABLE users
  ALTER mobile SET NOT NULL;

COMMIT;

Related blogs

Related by category