ALTER TABLE patch

12 December 2005   0 comments   Work

Mind That Age!

This blog post is 13 years old! Most likely, its content is outdated. Especially if it's technical.

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

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 by Keyword:
Best EXPLAIN ANALYZE benchmark script 19 April 2018
When Docker is too slow, use your host 11 January 2018
How do log ALL PostgreSQL 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
Related by Text:
jQuery and Highslide JS 08 January 2008
I'm back! Peterbe.com has been renewed 05 June 2005
Anti-McCain propaganda videos 12 August 2008
Ever wondered how much $87 Billion is? 04 November 2003
Guake, not Yakuake or Yeahconsole 23 January 2010