Make Postgres choose the next minimal available id

Question

I would like to make Postgres choose the first next available id so that no error occurs in the following case:

CREATE TABLE test(
id serial PRIMARY KEY,
name varchar
);

Then:

INSERT INTO test VALUES (2,'dd');
INSERT INTO test (name) VALUES ('aa');
INSERT INTO test (name) VALUES ('bb');

This will give a constraint error since id is primary.
How can I tell Postgres to insert the record with the next free id?

you don't need to tell it to choose. It will do automatically. Just suppress the id information. Of course, if the primary is serial. Postgresql will create a sequence to it — Jorge Campos, Jan 26 '15 at 14:55
@Jorge: I think OP already has out-of-order ID's in their database and wants to know how to fix things... — Kevin, Jan 26 '15 at 14:55
You shouldn't be providing manual values for a serial column. Why do you think you need to do that? — a_horse_with_no_name, Jan 26 '15 at 15:04
I understand, but in my scenario: the user can either specify an id he wishes or he doesn't specify an id — user4495098, Jan 26 '15 at 15:09
Take out the responsability of the user in controlling the ID. — Jorge Campos, Jan 26 '15 at 15:58
I took the liberty to fix the test setup. `"Test"` and `Test` would have been referring to *different* tables. — Erwin Brandstetter, Jan 27 '15 at 12:55

score 9 · Accepted Answer · edited May 23 '17 at 11:45

Generally it's best to never overrule the default in a serial column. If you sometimes need to provide id values manually, replace the standard DEFAULT clause nextval('sequence_name') of the serial column with a custom function that omits existing values.

Based on this dummy table:

CREATE TABLE test (test_id serial PRIMARY KEY, test text);

Function:

CREATE OR REPLACE FUNCTION f_test_test_id_seq(OUT nextfree bigint) AS
$func$
BEGIN
LOOP
   SELECT INTO nextfree  val
   FROM   nextval('test_test_id_seq'::regclass) val  -- use actual name of sequence
   WHERE  NOT EXISTS (SELECT 1 FROM test WHERE test_id = val);

   EXIT WHEN FOUND;
END LOOP; 
END
$func$  LANGUAGE plpgsql;

Alter default:

ALTER TABLE test ALTER COLUMN test_id SET DEFAULT f_test_test_id_seq();

It's not strictly a serial any more, but serial is only a convenience feature anyway:

Safely and cleanly rename tables that use serial primary key columns in Postgres?

And if you build this on top of a serial column the SEQUENCE is automatically "owned" by the table column, which is probably a good thing.

This is a slightly faster variant of:

Autoincrement, but omit existing values in the column

Table and sequence name are hard coded here. You could easily parametrize the sequence name (like in the linked answer) and even the table name - and test existence with a dynamic statement using EXECUTE. Would give you a generic function, but the call would be a bit more expensive.

CREATE OR REPLACE FUNCTION f_nextfree(_tbl regclass
                                     , _col text
                                     , _seq regclass
                                     , OUT nextfree bigint) AS
$func$
BEGIN
LOOP
   EXECUTE '
   SELECT val FROM nextval($1) val WHERE NOT EXISTS (
      SELECT 1 FROM ' || _tbl || ' WHERE ' || quote_ident(_col) || ' = val)'
   INTO  nextfree
   USING _seq;

   EXIT WHEN nextfree IS NOT NULL;
END LOOP; 
END
$func$  LANGUAGE plpgsql;

ALTER TABLE test2 ALTER COLUMN test2_id
SET DEFAULT f_nextfree('test2', 'test2_id', 'test2_test2_id_seq');

SQL Fiddle.

Make Postgres choose the next minimal available id

1 Answers1

Linked