Thread: [GENERAL] Adding identity column to a non-empty table

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;

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         
ADDGENERATED 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin,

On 10/15/2017 5:56 PM, Melvin Davidson wrote:

On Sun, Oct 15, 2017 at 8:24 PM, Igal @ Lucee.org <igal@lucee.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

On 10/15/2017 6:42 PM, Melvin Davidson wrote:

On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <igal@lucee.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,