Reset postgres sequence to take un-used primary key ids

Question

I am using postgres 9.5. As part of application initialization I make some inserts in database at application startup with random ids. Something like insert into student values(1,'abc') , insert into student values(10,'xyz'). Then I have some rest APIs developed which insert new rows programatically. Is there any way we can tell postgres to skip already taken ids?

It tried to take up already used ids. I noticed it does not have the sequence updated accounting for the initial inserts

Here is how I create the table

CREATE TABLE student(
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(64) NOT NULL UNIQUE     
);

Added table definition. Is it still possible to insert with ids 1 and then 10 and still skip 10 when I try to insert using APIs? in APIs I use insert into student values(default, 'def') instead of numeric ids. — codec
– codec, Commented Nov 18, 2016 at 12:38

David Aldridge · Accepted Answer · 2016-11-18 13:05:45Z

2

It sounds like you might be better served with UUIDs as your primary key values, if your data is distributed.

answered Nov 18, 2016 at 13:05

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

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

Comments

score 1 · Accepted Answer · 2016-11-18 12:59:17Z

1

You can advance the sequence that is populating the id column to the highest value:

insert into student (id, name) 
values 
  (1, 'abc'),
  (2, 'xyz');

select setval(pg_get_serial_sequence('student', 'id'), (select max(id) from student));

edited Nov 18, 2016 at 12:59

answered Nov 18, 2016 at 12:41

user330315

6 Comments

codec Over a year ago

I agree this would work. But here is the issue - the data i have in my DB is distributed. I have ids. 1 , 10 , 20 , 30 , 40 and so on. Is it possible to have id=2 to 9 and then jump to 11?

user330315 Over a year ago

@love2code: that is a completely new question. Yes you can do that if you control the sequence manually and use a increment of 2 instead of 1

codec Over a year ago

Sorry if the question was not very clear but that was my original question. ++2 will work if the sequence is sure to be skipped by 1 value. but if I have random ids. fr eg: 10,20,21,30,31,32,33,35,40,45 and so on ++2 won't work

user330315 Over a year ago

@love2code: sorry you lost me there. I have no idea what you are trying to do.

joop Over a year ago

@a_horse_with_no_name I think he wants to avoid holes. non-issue. sigh

|

Collectives™ on Stack Overflow

Reset postgres sequence to take un-used primary key ids

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related