Re: 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