deferring pk constraint

On Tue, 15 Nov 2011 18:56:37 -0700, J.V. wrote:

I have a table with existing data for which I need to:

1) drop the single primary key column (int4)
2) recreate the column with the pk (not null) constraint deferred
3) repopulate the column from a sequence
4) enable the constraint

When I issue this command to add the column:

alter table <table_name> add column id INT4 NOT NULL;

I get an error saying:

ERROR: column "id" contains null values.

Is there a way to issue the "alter table..." command and defer the
constraint (did not see in online docs)
and then at some point enable it?

What would be the best approach here?

Create the sequence first and create the new column with a default.

alter table foo drop constraint foo_pkey;
create sequence foo_id_seq;
alter table foo add id bigint default nextval('foo_id_seq'::regclass);
alter sequence foo_id_seq owned by foo.id;
alter table foo add primary key (id) deferrable;

Sequences use bigint, rather than int4, so your
new key column should be bigint.

--
nw