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.

Previous:
iWipe - toilet paper and a Mac December 11, 2005 Misc. links
Next:
Geek entrepreneurs' reading list December 13, 2005 Books
Related by category:
I'm a GitHubber now September 21, 2021 Work
Recruiters: if you're going to lie, do it properly April 7, 2013 Work
An idea for a better timesheet tracker January 12, 2006 Work
Sorting transform function in PostgreSQL August 3, 2006 Work
Related by keyword:
Adding client-to-server sync to PissueTracker March 20, 2025 React, JavaScript, Bun
Connecting with psycopg2 without a username and password February 24, 2011 Python
UPPER vs. ILIKE April 19, 2010 Web development
How I performance test PostgreSQL locally on macOS December 10, 2018 Web development, PostgreSQL, macOS