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?

Answer 1

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.