PostgreSQL SERIAL: Auto-Incrementing Columns

SERIAL is PostgreSQL-specific shorthand for an integer column with a sequence-backed default. It is not a distinct storage type, and it does not make the column unique by itself. Add a PRIMARY KEY or UNIQUE constraint when the generated value must identify each row. See PostgreSQL’s Serial Types documentation.

Create a table with a SERIAL column

Add SERIAL as the column type. This example also declares the column as the table’s primary key:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  description TEXT NOT NULL
);

When PostgreSQL creates the table, it uses an integer column with a NOT NULL default that obtains values from a sequence. The primary key in this example supplies the uniqueness constraint; SERIAL alone would not.

Choose a SERIAL size

PostgreSQL provides three sequence-backed integer shorthands:

Use BIGSERIAL if an integer sequence may exceed the SERIAL range. SERIAL is shorthand for an INTEGER; BIGSERIAL is shorthand for a BIGINT.

Insert rows and return the generated ID

Leave the SERIAL column out of the insert column list and PostgreSQL uses its default sequence value. RETURNING gives the ID created for that specific row:

INSERT INTO orders (description)
VALUES ('Replacement filter')
RETURNING order_id;

You can also request the generated value explicitly with DEFAULT:

INSERT INTO orders (order_id, description)
VALUES (DEFAULT, 'Replacement filter')
RETURNING order_id;

PostgreSQL documents RETURNING for retrieving values from modified rows in the INSERT reference.

How PostgreSQL implements SERIAL

Conceptually, SERIAL expands to an integer column with a sequence default and an ownership link between the sequence and the column:

CREATE SEQUENCE orders_order_id_seq AS INTEGER;

CREATE TABLE orders (
  order_id INTEGER NOT NULL DEFAULT nextval('orders_order_id_seq')
);

ALTER SEQUENCE orders_order_id_seq OWNED BY orders.order_id;

The sequence is dropped when its owning column or table is dropped. PostgreSQL generates a sequence name from the table and column names, but applications should not hard-code that name. Use pg_get_serial_sequence() to find the sequence associated with a column:

SELECT pg_get_serial_sequence('orders', 'order_id');

To read the sequence’s current session value after nextval() has been called, use currval() with that sequence name. For inserts, INSERT ... RETURNING is usually simpler because it returns the ID from the same statement. See PostgreSQL’s sequence function reference.

SERIAL or IDENTITY?

PostgreSQL also supports the SQL-standard identity syntax:

CREATE TABLE orders (
  order_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  description TEXT NOT NULL
);

GENERATED ALWAYS rejects explicitly supplied identity values unless the insert uses OVERRIDING SYSTEM VALUE. GENERATED BY DEFAULT accepts an explicit value when one is provided. Both identity modes use an implicit sequence and still need a primary key or unique constraint if values must be unique. See PostgreSQL identity columns and the Identity Columns documentation.

SERIAL remains supported and is convenient for existing schemas. Identity columns follow the SQL standard and make the generation rule more explicit in new schemas.

Sequence values can have gaps

Sequence values are allocated independently of transaction rollback. If an insert obtains a value and the transaction later rolls back, PostgreSQL does not put that number back into the sequence. Gaps can therefore appear even when no rows were deleted. Do not use a SERIAL sequence when a business requirement needs gapless numbering.

For related PostgreSQL topics, see identity columns and sequence functions.