Asking Postgres to add a non-existent sequence value

Question

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?

Juan Carlos Oropeza · Accepted Answer · 2016-05-25 03:12:14Z

1

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.

edited May 25, 2016 at 3:12

answered May 25, 2016 at 2:47

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

anipard Over a year ago

For my case, what if I cannot change the existing profile_id. What I can do is to add another. Sorry if it's not very clear from the start. I already edit my question.

Juan Carlos Oropeza Over a year ago

What line give you error? Is that insert you show just after create table ? or much later? As I explain you can use ALTER SEQUENCE so the next number is > max(current_id)

Juan Carlos Oropeza Over a year ago

You can also try CURVAL() to know what is current seq value. But you also has to remove the serial field from insert

anipard Over a year ago

It gave error when I insert new data, ie: INSERT INTO profiles (num_feed, num_email, name) VALUES (2, 4, 'John Doe'); I'll try ALTER_SEQUENCE.

anipard Over a year ago

I decide to change the start value to table max value. Thank you.

Collectives™ on Stack Overflow

Asking Postgres to add a non-existent sequence value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related