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?

Cordeelia

Are you sure, you really need this? – Athalia

Define id as the primary key, and you can't insert the same value twice. – Herbage

This recent related question on dba.SE may be of help. Generally: don't insert values manually into a serial column. – Stricklin

I need such solution. customer wants to get rid from ASA database and wants to copy its data to psql. – Cordeelia

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.

Supernatant

This answer should be a comment, but Stack Overflow has annoying restrictions on that for new users. Anyway: your proposed approach isn't safe in the face of concurrent inserts, because what your transaction sees as "free" might've already been allocated to another uncommitted transaction. (I agree that just handing a unique violation might be cleaner). – Atkinson

I think this has enough substance to pass as answer in its own right. What you write is the standard procedure for excluding dupes. There was also the question how to make auto-increment jump over '5'? And that's what my answer does. It should perform well with few islands and many gaps (which seems to be the case according to the example). To enforce uniqueness, add a constraint. – Stricklin

Answer 2

Update: Later, more detailed answer:

• Make Postgres choose the next minimal available id

---

This should work smoothly:

CREATE OR REPLACE FUNCTION f_next_free(_seq regclass)
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      PERFORM nextval(_seq);
      EXIT WHEN NOT EXISTS (SELECT FROM db.t1 WHERE id = lastval());
   END LOOP; 

   RETURN lastval();
END
$func$;

Loop fetching the next number from a given sequence until one is found that is not yet in the table. Should even be safe for concurrent use, since we still rely on a sequence.

Use this function in the column default of the serial column (replacing the default for the serial columns nextval('t1_id_seq'::regclass):

ALTER TABLE db.t1 ALTER COLUMN id
SET DEFAULT f_next_free('t1_id_seq'::regclass);

The manual on lastval().

This performs well with few islands and many gaps (which seems to be the case according to the example). To enforce uniqueness, add a unique constraint (or primary key) on the column.

Stricklin

Huh. Nice. It's not going to be gapless, since a row ID lost by rollback won't be re-used, but it's concurrency safe. – Atkinson

Answer 3

You can implement a trigger function on inserting. This function will chck if NEW.id is not null and update the sequence related to the id field.

IF NEW.id IS NOT NULL THEN SELECT SETVAL(sequence_name_of_id_field,NEW.id);
END IF;

Jorge

What keeps me from entering a row with id = 10, and later id = 8? – Stricklin

Then, you must use a trigger function instead of a sequence. When inserts a row, if id is null, search the max id of table and assign the id of new inserted row. – Jorge

Trigger +max() isn't concurrency safe. You'd have to LOCK TABLE ... IN EXCLUSIVE MODE first. And no, you can't do that within the trigger, you'll get frequent deadlocks due to lock upgrades. – Atkinson