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?

Define `id` as the primary key, and you can't insert the same value twice. — , Mar 14 '14 at 14:27
[This recent related question on dba.SE](http://dba.stackexchange.com/questions/60802/fixing-table-structure-to-avoid-error-duplicate-key-value-violates-unique-cons/60807) may be of help. Generally: don't insert values manually into a serial column. — Erwin Brandstetter, Mar 14 '14 at 14:31
I need such solution. customer wants to get rid from ASA database and wants to copy its data to psql. — user2301515, Mar 14 '14 at 14:38

Erwin Brandstetter · Answer 1 · 2023-06-30T22:21:22.160

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.

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. — Craig Ringer, Mar 15 '14 at 00:49

score 2 · Answer 2 · answered Mar 14 '14 at 16:51

2

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;

answered Mar 14 '14 at 16:51

Isidoro Arroyo Barrantes

355
1
11

What keeps me from entering a row with `id = 10`, and later `id = 8`? – Erwin Brandstetter Mar 14 '14 at 17:09
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. – Isidoro Arroyo Barrantes Mar 14 '14 at 17:13
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. – Craig Ringer Mar 15 '14 at 00:50

score 0 · Accepted Answer · answered Mar 14 '14 at 21:00

0

@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.

answered Mar 14 '14 at 21:00

Nick

123
8

4

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). – Craig Ringer Mar 15 '14 at 00:53
1

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. – Erwin Brandstetter Mar 15 '14 at 10:24

Autoincrement, but omit existing values in the column

3 Answers3

Linked