ALTER TABLE patch

12 December 2005   0 comments   Work

Powered by Fusion×

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

Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
iWipe - toilet paper and a Mac 11 December 2005
Next:
Geek entrepreneurs' reading list 13 December 2005
Related:
How do log ALL PostgresSQL SQL happening 20 July 2015
Fastest database for Tornado 09 October 2013
Postgres collation errors on CITEXT fields when upgrading to 9.1 21 May 2012
Connecting with psycopg2 without a username and password 24 February 2011
Optimization of getting random rows out of a PostgreSQL in Django 23 February 2011
Fastest "boolean SQL queries" possible with Django 14 January 2011
UPPER vs. ILIKE 19 April 2010
Speed test between django_mongokit and postgresql_psycopg2 09 March 2010
To sub-select or not sub-select in PostgreSQL 31 August 2009
Why bother with MySQL... 09 October 2008