Search Postgresql Archives

Re: Adding identity column to a non-empty table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux