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;
Comments