Re: Adding identity column to a non-empty table

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
> <mailto: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