Do you train Kung Fu?
Or know someone who does?
Then check out KungFuPeople.com
Mobile version of this pageiWipe - toilet paper and a Mac
Next:
Geek entrepreneurs' reading list
Related blogs
PSP - Python Server PagesPostgreSQL, MySQL or SQLite
Creating a user for postgresql
Adding a year in PostgreSQL
Date formatting in python or in PostgreSQL
Integer division in programming languages
Running simple SQL commands on the command line
pg_class to check if table exists
List of casts in PostgreSQL
Just Oracle and IBM?
Quick PostgreSQL optimization story
Date formatting in Python or in PostgreSQL (part II)
Sorting transform function in PostgreSQL
Why bother with MySQL...
To sub-select or not sub-select in PostgreSQL
Speed test between django_mongokit and postgresql_psycopg2
UPPER vs. ILIKE
Fastest "boolean SQL queries" possible with Django
Optimization of getting random rows out of a PostgreSQL in Django
Connecting with psycopg2 without a username and password
Related by category
Work
ALTER TABLE patch
11th of December 2005
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:
...won't work and you'll get an error.
Suppose for example that you created a table like this:
"uid" SERIAL PRIMARY KEY,
"password" VARCHAR(20) NOT NULL,
"email" VARCHAR(100) NOT NULL DEFAULT ''
);
But what you really want
is this:
"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:
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;
