On 10/15/2017 6:42 PM, Melvin Davidson wrote:
On Sun, Oct 15, 2017 at 9:09 PM, Igal @ Lucee.org <i...@lucee.org
<mailto:i...@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 <http://lucee.org/>