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

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 by Keyword:
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
Connecting with psycopg2 without a username and password 24 February 2011
Related by Text:
HTML Tree on Hacker News 18 May 2014
YouTube - Nigella's XXXmas 19 December 2008
How and why to use django-mongokit (aka. Django to MongoDB) 08 March 2010
How to track Google Analytics pageviews on non-web requests (with Python) 03 May 2016
hashin 0.7.0 and multiple packages 30 August 2016