SERIAL in PostgreSQL

PostgreSQL’s SERIAL pseudo-type provides a convenient shorthand for auto-incrementing integer columns. It is most commonly used for primary key columns where the database should assign unique IDs automatically. Under the hood, PostgreSQL creates a SEQUENCE object, sets the column default to nextval(), and adds NOT NULL — all in one step.

Syntax

CREATE TABLE table_name (
  id SERIAL [PRIMARY KEY],
  ...
);

This is exactly equivalent to:

CREATE SEQUENCE table_name_id_seq;
CREATE TABLE table_name (
  id INTEGER NOT NULL DEFAULT nextval('table_name_id_seq')
);
ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;

The three SERIAL variants:

Practical Example

Create a products table with a SERIAL primary key:

CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  sku         VARCHAR(50)    NOT NULL UNIQUE,
  name        VARCHAR(200)   NOT NULL,
  unit_price  NUMERIC(10,2)  NOT NULL DEFAULT 0
);

Insert rows by omitting the product_id column:

INSERT INTO products (sku, name, unit_price)
VALUES
  ('WID-001', 'Basic Widget',  9.99),
  ('WID-002', 'Premium Widget', 24.99);

Use RETURNING to capture the generated ID immediately:

INSERT INTO products (sku, name, unit_price)
VALUES ('WID-003', 'Deluxe Widget', 49.99)
RETURNING product_id;

 product_id
------------
          3
(1 row)

Check the current sequence value:

SELECT currval(pg_get_serial_sequence('products', 'product_id'));

Add a SERIAL primary key to an existing table:

ALTER TABLE categories ADD COLUMN category_id SERIAL PRIMARY KEY;

SERIAL vs GENERATED AS IDENTITY

For new tables, prefer the SQL-standard GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+):

-- Modern approach
product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- Legacy shorthand
product_id SERIAL PRIMARY KEY

GENERATED ALWAYS AS IDENTITY prevents accidental overwrites — an insert that supplies an explicit value raises an error unless you use OVERRIDING SYSTEM VALUE.

Testing with Vela

When migrating a schema from SERIAL to GENERATED AS IDENTITY, or when resetting sequences after a bulk data load, Vela database branching lets you run and validate the migration on a full copy of production data before applying it to the live database. Test the sequence reset, verify no duplicate-key errors on subsequent inserts, then apply to production.

Production Tips

• SERIAL does not automatically create a primary key — add PRIMARY KEY explicitly.
• Prefer BIGSERIAL for tables expected to grow beyond a few hundred million rows to avoid sequence exhaustion.
• Sequence values are not transaction-safe: rolled-back transactions permanently skip the consumed value, creating gaps. This is normal — never write business logic that assumes gap-free IDs.
• After bulk data imports with explicit IDs, resync the sequence: SELECT setval(pg_get_serial_sequence('products', 'product_id'), MAX(product_id)) FROM products;
• For new tables, use GENERATED ALWAYS AS IDENTITY instead of SERIAL — it is the SQL standard and safer against accidental manual inserts.