| From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
|---|---|
| To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
| Subject: | cluster on table rewrite |
| Date: | 2016-09-28 20:23:42 |
| Message-ID: | CAMkU=1wOg4JPi0YGSjmmYj+eioUCL9RWP4US+4ug1aSSYc9Cow@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | dudedoe01 | 2016-09-28 21:24:40 | Re: isnull() function in pgAdmin3 |
| Previous Message | Scott Mead | 2016-09-28 20:22:04 | Re: Graphical entity relation model |