Make PostgreSQL choose the next minimal available id

Question

I would like to make PostgreSQL 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 PostgreSQL to insert the record with the next free id?

Grandiloquence

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 – Leanaleanard

@Jorge: I think OP already has out-of-order ID's in their database and wants to know how to fix things... – Limeade

You shouldn't be providing manual values for a serial column. Why do you think you need to do that? – Jameljamerson

I understand, but in my scenario: the user can either specify an id he wishes or he doesn't specify an id – Grandiloquence

Take out the responsability of the user in controlling the ID. – Leanaleanard

I took the liberty to fix the test setup. "Test" and Test would have been referring to different tables. – Septempartite

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 OR REPLACE FUNCTION f_test_test_id_seq(OUT nextfree bigint)
  LANGUAGE plpgsql 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 FROM test WHERE test_id = val);

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

Use it in column 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 rename tables using serial primary key columns

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

And it's equally safe against conflicts under concurrent write load.

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)
  LANGUAGE plpgsql AS
$func$
BEGIN
   LOOP
      EXECUTE '
      SELECT val FROM nextval($1) val WHERE NOT EXISTS (
         SELECT FROM ' || _tbl || ' WHERE ' || quote_ident(_col) || ' = val)'
      INTO  nextfree
      USING _seq;

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

Use it in column default:

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

fiddle
_{Old sqlfiddle}

Septempartite