CODEWITHSUNDEEP
postgresqlauto-increment
anipard
anipard

Reputation: 164

Asking Postgres to add a non-existent sequence value

I have an existing Postgres table, for example:

CREATE TABLE profiles(
  profile_id    SERIAL PRIMARY KEY,
  num_feed      integer, 
  num_email     integer, 
  name          text
);

ALTER TABLE ONLY profiles ALTER COLUMN profile_id SET DEFAULT nextval('profiles_profile_id_seq'::regclass);
ALTER TABLE ONLY profiles ADD CONSTRAINT profiles_pkey PRIMARY KEY (profile_id);
CREATE INDEX "pi.profile_id" ON profiles USING btree (profile_id);

And this is the existing data, which I can't change, I can only add a new ones.

INSERT INTO profiles VALUES
(3, 2, 5, 'Adam Smith'),
(26, 2, 1, 'Fran Morrow'),
(30, 2, 2, 'John Doe'),
(32, 4, 1, 'Jerry Maguire'),
(36, 1, 1, 'Donald Logue');

The problem is when I tried to insert a new data, Postgres will add a minimum value (which is good) on column "profile_id" but will failed/error when it hits an existent value, because that value exists.

ERROR:  duplicate key value violates unique constraint "profile_id"
DETAIL:  Key (profile_id)=(3) already exists.

Is it possible to ask Postgres to add a next non-existent value?

Upvotes: 1

Views: 358

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Dont specify the SERIAL field on the insert sentence, let postgres generate it from the sequence for you.

INSERT INTO profiles 
 (num_feed,  num_email, name)
VALUES
(2, 5, 'Adam Smith'),
(2, 1, 'Fran Morrow'),
(2, 2, 'John Doe'),
(4, 1, 'Jerry Maguire'),
(1, 1, 'Donald Logue');

NOTE: this can fail if after a while you reset the sequence, something like

ALTER SEQUENCE 'profiles_profile_id_seq' RESTART WITH 1;

Next insert will try to create 1 again and fail.

Upvotes: 1

Related Questions