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 03:10:22 |
Message-ID: | 959f28ef-4245-8349-eff9-0ff5f666df03@lucee.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/15/2017 6:42 PM, Melvin Davidson wrote:
> On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <igal(at)lucee(dot)org
> <mailto:igal(at)lucee(dot)org>> wrote:
>
> Melvin,
>
> On 10/15/2017 5:56 PM, Melvin Davidson 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".
>
>
> > 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.
Understood. But I already had a an auto-increment column by way of
BIGSERIAL.
I want specifically to use the new IDENTITY feature of Postgres 10.
Best,
Igal Sapir
Lucee Core Developer
Lucee.org <http://lucee.org/>
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2017-10-16 06:53:38 | Re: Delete Duplicates with Using |
Previous Message | Melvin Davidson | 2017-10-16 01:42:46 | Re: Adding identity column to a non-empty table |