Autoincrement, but omit existing values in the column

Question

I have a table:

create table DB.t1 (id  SERIAL,name varchar(255));

and insert some data:

insert into DB.t1 (name) values ('name1');
insert into DB.t1 (id,name) values (5,'name2');
insert into DB.t1 (name) values ('name3');
insert into DB.t1 (name) values ('name4');
insert into DB.t1 (name) values ('name5');
insert into DB.t1 (name) values ('name6');
insert into DB.t1 (name) values ('name7');
select * from DB.t1;

Then I can see:

1 name1
5 name2
2 name3
3 name4
4 name5
5 name6   -- how to make auto-increment jump over '5'?
6 name7

But 5 is present two times. How to keep id unique?

Answer 1

@erwin-brandstetter Won't it be faster to first find the missing value and then simply setval('t1_id_seq'::regclass, ), thus removing excessive nextval calls? Also, if the question is how to make ids unique, assigning this code for default value won't solve the problem.

I'd suggest using unique constraint or primary key constraint and handle unique violation exception.