From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | "Igal (at) Lucee(dot)org" <igal(at)lucee(dot)org> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding identity column to a non-empty table |
Date: | 2017-10-16 00:56:33 |
Message-ID: | CANu8Fizi9KmxeYe=7C5v81gfUKB00egCrw4axvkOmpox5EeeJQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Oct 15, 2017 at 8:24 PM, Igal @ Lucee.org <igal(at)lucee(dot)org> wrote:
> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
The correct way to make r_id the primary key would be:
ALTER TABLE event_log
ADD COLUMN r_id SERIAL;
ALTER TABLE event_log
ALTER COLUMN r_id TYPE BIGINT,
ADD CONSTRAINT dummy_pk PRIMARY KEY (r_id);
That automatically generates the column as
r_id bigint NOT NULL DEFAULT nextval('dummy_r_id_seq'::regclass),
CONSTRAINT dummy_pk PRIMARY KEY (r_id)
and creates the appropriate sequence for you.
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From | Date | Subject | |
---|---|---|---|
Next Message | Igal @ Lucee.org | 2017-10-16 01:09:15 | Re: Adding identity column to a non-empty table |
Previous Message | Igal @ Lucee.org | 2017-10-16 00:24:37 | Re: Adding identity column to a non-empty table |