From: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding identity column to a non-empty table |
Date: | 2017-10-16 00:24:37 |
Message-ID: | 7435ef5f-f662-15f0-6ebc-b4b1b7cf1156@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/15/2017 4:01 PM, Igal @ Lucee.org wrote:
>
> Hello,
>
> I'm trying to add an identity column to a table that has records
> (previously had a bigserial column which I removed):
>
> ALTER TABLE event_log
> ADD COLUMN r_id BIGINT GENERATED BY DEFAULT AS IDENTITY;
>
> But I'm getting an error `column r_id contains null values`.
>
> How can I add the column and populate it for the existing rows?
>
There is probably a better solution, but the one I came up with is to
add the column as BIGSERIAL and DROP the SEQUENCE CASCADE, SELECT the
max(rid) + 1, and then convert the column to IDENTITY:
ALTER TABLE transient.event_log ADD COLUMN r_id BIGSERIAL;
-- find the sequence name and then
DROP sequence <sequence-name> CASCADE;
-- find min value by executing select max(r_id) + 1
ALTER table transient.event_log
ALTER COLUMN r_id
ADD GENERATED BY DEFAULT AS IDENTITY (MINVALUE <min-value>);
If anyone has a better suggestion then please let me know.
Thanks,
Igal
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2017-10-16 00:56:33 | Re: Adding identity column to a non-empty table |
Previous Message | Igal @ Lucee.org | 2017-10-15 23:01:40 | Adding identity column to a non-empty table |