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 01:42:46 |
Message-ID: | CANu8Fiwh9SxM5t4BwFr=5F2iD6BW5zY4-o6FRgHHkK79g80ENg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <igal(at)lucee(dot)org> wrote:
> Melvin,
>
> On 10/15/2017 5:56 PM, Melvin Davidson wrote:
>
>
> 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):
>>>
>>>
>> 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:
>>
>>
> 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.
>
>
> Does that use the new IDENTITY construct that was added in Postgres 10? I
> do not really care for the PRIMARY KEY constraint. I just want the
> sequence with the benefits of the new IDENTITY "type".
>
> Thanks,
>
>
> Igal
>
> Does that use the new IDENTITY construct that was added in Postgres 10?
I cannot say, as I do not yet have PostgreSQL 10 installed because it was
very recently released.
However, the method I supplied works for all prior versions of PostgreSQL.
--
*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 03:10:22 | Re: Adding identity column to a non-empty table |
Previous Message | Igal @ Lucee.org | 2017-10-16 01:09:15 | Re: Adding identity column to a non-empty table |