I need to add a new column to my largest table, something like:
alter table foo add column col_15 text not null default 'foobar';
I am tempted to add the column as NULL, and then use coalesce and nullif in the client code to re-interpret NULL as being the value 'foobar'. But I think that that would be penny wise and pound foolish, so am willing to bite the bullet of doing a table rewrite.
But while I am rewriting it anyway, is there a way to get it to re-CLUSTER on the cluster index, as one operation? The rewrite caused by the ADD COLUMN doesn't automatically cluster.
The closest I can hit upon is to do something like:
create table foo_new as select *, 'foobar'::text as col_15 from foo order by col_8;
But then I have to manually juggle renaming tables and foreign key constraints and such.
Is there a better way?
Cheers,
Jeff