Re: [GENERAL] Adding identity column to a non-empty table - Mailing list pgsql-general
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):
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
ADD GENERATED 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
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
ADD COLUMN r_id SERIAL;
ALTER TABLE event_log
ALTER COLUMN r_id TYPE BIGINT,
ADD CONSTRAINT dummy_pk PRIMARY KEY (r_id);
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)
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: