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

[Igal @ Lucee.org]

On 10/15/2017 6:42 PM, Melvin Davidson wrote:
On Sun, Oct 15, 2017 at 9:09 PM, 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,

Igal Sapir
Lucee Core Developer
Lucee.org 

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

[Melvin Davidson]

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

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

[Igal @ Lucee.org]

Melvin,

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


On Sun, Oct 15, 2017 at 8:24 PM, 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

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

[Melvin Davidson]

On Sun, Oct 15, 2017 at 8:24 PM, 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):
>>
>>   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  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 );
>
> 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
>

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.

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

[Igal @ Lucee.org]

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  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 );

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

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

[Igal @ Lucee.org]

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?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org