Search code examples
databasepostgresqldatabase-sequence

Column ID skipped whenever it encounter a data duplicate

I have a table named email_recipients and an ID is skipped whenever there is a duplicate in email. 

CREATE TABLE email_recipients(
id SERIAL PRIMARY KEY,
email_address varchar(255) UNIQUE NOT NULL,
last_name varchar(255),
first_name varchar(255),
active boolean
);
Solution

  • Yes, that is normal.

    The DEFAULT value has to be evaluated before the insertion, and if the INSERT fails for any reason, the sequence value still has been consumed.

    This is because sequences are non-transactional for performance reasons, which is a feature.

    Don't strive for a “gap-less” sequence, it offers no benefit, only pain.