is there a way to avoid calling nextval() if the insert fails in PostgreSQL?

Question

In a PostgreSQL database I have a table with a primary key and another field which needs to be unique.

  CREATE TABLE users (
    id      INTEGER PRIMARY KEY DEFAULT nextval('groups_id_seq'::regclass),
    name    VARCHAR(255) UNIQUE NOT NULL
  );

  INSERT users (name) VALUES ('foo');
  INSERT users (name) VALUES ('foo');
  INSERT users (name) VALUES ('bar');

The second insert fails but the sequence groups_id_seq is already incremented so when 'bar' is added it leaves a gap in the id numbers.

Is there a way to tell PostgreSQL to fetch the next value only if other constraints are met or should I check first using SELECT if the name is not duplicate? This still would not guarantee the lack of gaps but at least it would reduce their number to the rare cases when there is another process trying to insert the same name at the same time

Answer 1

I don't think so: a basic feature of sequences is that gaps are possible (think of two concurrent transactions, with one performing a ROLLBACK). You should ignore gaps. Why are they a problem in your case?