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