From: | Nathan Wagner <nw(at)hydaspes(dot)if(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: deferring pk constraint |
Date: | 2011-11-16 02:32:01 |
Message-ID: | 6dd548c3fba68a3743d5d4dc05543d8f@granicus.if.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2011-11-16 03:04:33 | Re: how to drop function? |
Previous Message | Scott Marlowe | 2011-11-16 01:57:25 | Re: how to drop function? |