PostgreSQL - Auto Increment

PostgreSQL - Auto-Incrementing Columns

Creating a column that automatically generates a unique number for each new row is a common requirement, especially for primary keys. PostgreSQL provides two main mechanisms for this: the modern, SQL-standard IDENTITY columns, and the traditional, PostgreSQL-specific SERIAL pseudo-types.

Best Practice: For new applications (PostgreSQL v10+), it is highly recommended to use IDENTITY columns as they are more robust and follow the SQL standard.

The Modern Approach: IDENTITY Columns

Introduced in PostgreSQL 10, the IDENTITY constraint is the standard way to create an auto-incrementing column. It attaches a sequence generator to a column, making it more explicit and configurable than the older SERIAL type.

Syntax

CREATE TABLE table_name (
    column_name BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY
    -- or GENERATED BY DEFAULT AS IDENTITY
);

There are two options for generation:

• GENERATED ALWAYS AS IDENTITY: This is the stricter and often safer option. The database will always generate a value. Attempting to manually INSERT a value into this column will cause an error unless you explicitly override it.
• GENERATED BY DEFAULT AS IDENTITY: This is more flexible. The database will generate a value only if one is not provided in the INSERT statement. This can be useful for data migration but can lead to conflicts if not managed carefully.

Example

CREATE TABLE employees (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

-- The 'id' column does not need to be specified in the INSERT statement.
INSERT INTO employees (first_name, last_name) VALUES ('Alice', 'Smith');
INSERT INTO employees (first_name, last_name) VALUES ('Bob', 'Johnson');

The resulting table will have automatically generated id values:

 id | first_name | last_name
----+------------+-----------
  1 | Alice      | Smith
  2 | Bob        | Johnson
(2 rows)

The Legacy Approach: SERIAL Types

Before version 10, SERIAL was the standard method in PostgreSQL. It is not a true data type, but a shorthand notation that creates an integer column, creates a sequence, and sets the column’s default value to the next value from that sequence.

The available types are SMALLSERIAL (2 bytes), SERIAL (4 bytes), and BIGSERIAL (8 bytes). BIGSERIAL is the safest choice for primary keys to avoid running out of numbers.

Syntax

CREATE TABLE legacy_products (
    id BIGSERIAL PRIMARY KEY,
    product_name TEXT NOT NULL
);

This is essentially a shortcut for:

CREATE SEQUENCE legacy_products_id_seq AS BIGINT;
CREATE TABLE legacy_products (
    id BIGINT PRIMARY KEY NOT NULL DEFAULT nextval('legacy_products_id_seq'),
    product_name TEXT NOT NULL
);
ALTER SEQUENCE legacy_products_id_seq OWNED BY legacy_products.id;

Choosing Between IDENTITY and SERIAL

Feature	IDENTITY (Recommended)	SERIAL (Legacy)
SQL Standard	Yes	No (PostgreSQL specific)
Explicitness	Clear and explicit syntax (GENERATED ... AS IDENTITY).	Acts as a magical pseudo-type, hiding the underlying sequence.
Permissions	Follows standard column permissions.	Users need USAGE permission on the underlying sequence, which can be overlooked.
Override Behavior	Clearly defined (ALWAYS vs. BY DEFAULT).	Acts like BY DEFAULT, can always be manually overridden, which can lead to silent errors.

Alternative: Using UUID

For distributed systems where sequential IDs can cause conflicts or reveal information, using a Universally Unique Identifier (UUID) is a robust alternative. You can auto-generate them using the gen_random_uuid() function (from the pgcrypto extension, which is often enabled by default).

CREATE TABLE events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type TEXT NOT NULL,
    payload JSONB
);