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, Nov 18 '16 at 12:38

score 2 · Answer 1 · answered Nov 18 '16 at 13:05

2

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

answered Nov 18 '16 at 13:05

David Aldridge

51,479
8
68
96

score 1 · Accepted Answer · 2016-11-18T12:59:17.057

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 '16 at 12:59

answered Nov 18 '16 at 12:41

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? – codec Nov 18 '16 at 12:56
@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 – Nov 18 '16 at 12:57
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 – codec Nov 18 '16 at 13:02
@love2code: sorry you lost me there. I have no idea what you are trying to do. – Nov 18 '16 at 13:05
@a_horse_with_no_name I think he wants to avoid holes. non-issue. *sigh* – joop Nov 18 '16 at 13:21
yes I just want to skip primary key ids which are already in use. So instead of getting an error duplicate_key while trying to insert it should just pick up the next available id. – codec Nov 18 '16 at 14:28

Reset postgres sequence to take un-used primary key ids

2 Answers2