I’ve just discovered my new favourite feature of the upcoming PostgreSQL 8.0: Let’s say, you have forgotten a column when creating the schema of a table. Let’s also say there already exist foreign kays referencing this table, so dropping and recreating it with the updated schema from your text-editor won’t work (or force you to recreate all other tables too).
So, you need alter table
Here’s what Postgres < 8 needs to add a column cdate which must be not null and have a default-value of current_timestamp:
alter table extart_prods add cdate timestamp; update extart_prods set cdate = current_timestamp; alter table extart_prods alter column cdate set not null; alter table extart_prods alter column cdate set default current_timestamp;
And here’s what it takes to do it in PostgreSQL 8:
alter table extart_prods add cdate timestamp not null default current_timestamp;
When typing this into psql, you’re so much faster. This is actually the only feature I really missed when going from MySQL to PostgreSQL for all bigger work
Oh and did I mention that in Postgres 8 (currently running Beta 4) the statement is executed noticably faster than in Postgres 7.4 (though this doesn’t really matter – you should not be altering production tables anyway)