Serial vs Identity Columns in PostgreSQL

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

If you’ve been using PostgreSQL for a while, you’ve probably written SERIAL or BIGSERIAL to create auto-incrementing columns. But since PostgreSQL 10, there’s a better way: identity columns.

In this post, we’ll cover:

• What SERIAL actually does - It’s not a real data type
• Identity column syntax - The SQL-standard approach
• Key differences - Permissions, safety, and schema management
• Migration guide - Converting existing SERIAL columns
• Which to choose - Practical recommendations

What SERIAL Actually Does

SERIAL isn’t a real data type—it’s a shorthand that expands to three separate operations:

-- When you write:
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- PostgreSQL actually executes:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
  id INTEGER NOT NULL DEFAULT nextval('users_id_seq')
);
ALTER SEQUENCE users_id_seq OWNED BY users.id;

This creates a separate sequence object and wires it up to your column with a default value. It works, but the indirection causes problems. The sequence is a separate database object with its own permissions, its own lifecycle, and its own set of quirks. When things go wrong—and they do—you’re debugging the interaction between two objects instead of one.

Identity Column Syntax

Identity columns, introduced in PostgreSQL 10, follow the SQL standard and treat the auto-increment behavior as an intrinsic property of the column:

CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

For most tables, you’ll want BIGINT to avoid running out of IDs:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

There are two variants of identity columns. GENERATED ALWAYS means PostgreSQL always generates the value—if you try to insert an explicit value, it fails unless you use special syntax. This is the safer choice because it prevents accidental manual inserts that could cause duplicate key errors later.

GENERATED BY DEFAULT generates a value only if you don’t provide one. You can insert explicit values without any special syntax, which is more flexible but offers less protection against mistakes. For most applications, GENERATED ALWAYS is the right choice.

Permissions

One of the most common SERIAL headaches is permissions. Because SERIAL creates a separate sequence object, you need to grant permissions on both the table and the sequence:

-- SERIAL: need both table and sequence permissions
GRANT INSERT ON users TO app_user;
GRANT USAGE ON SEQUENCE users_id_seq TO app_user;  -- Easy to forget!

Forget the sequence permission and your application gets “permission denied for sequence” errors in production. This is especially painful because it works fine in development (where you’re usually a superuser) and only fails when deployed with proper role separation.

With identity columns, table permissions are sufficient:

-- Identity: only need table permission
GRANT INSERT ON users TO app_user;

The sequence is an implementation detail that users don’t need to know about.

Schema Copying

When you copy a table structure with CREATE TABLE ... (LIKE ...), SERIAL columns cause surprising behavior:

CREATE TABLE users_backup (LIKE users INCLUDING ALL);
-- The new table references the ORIGINAL sequence!
-- Dropping users_backup doesn't drop the sequence
-- Dropping users breaks users_backup

The backup table shares the same sequence as the original, which is almost never what you want. Inserts into either table consume IDs from the shared sequence, and dropping the original table breaks the backup.

Identity columns handle this correctly:

CREATE TABLE users_backup (LIKE users INCLUDING ALL);
-- Creates a new, independent sequence for the backup table
-- Each table is fully independent

Each table gets its own sequence, so they’re truly independent. This matters for CI/CD pipelines that create test databases, schema migration tools, and any workflow that copies table structures.

Safety Against Manual Inserts

With SERIAL, nothing stops you from inserting an explicit ID value:

-- SERIAL: this works and may cause duplicate key errors later
INSERT INTO users (id, email) VALUES (999999, 'test@example.com');
-- Sequence is still at 5, will eventually try to use 999999 again

The sequence doesn’t know you manually used ID 999999. When it eventually increments to that value, you get a duplicate key error that’s hard to debug because it happens long after the problematic insert.

Identity columns with GENERATED ALWAYS prevent this:

-- GENERATED ALWAYS: this fails immediately
INSERT INTO users (id, email) VALUES (999999, 'test@example.com');
-- ERROR: cannot insert a non-DEFAULT value into column "id"

If you legitimately need to insert specific IDs (during data migration, for example), you can explicitly override:

INSERT INTO users (id, email)
OVERRIDING SYSTEM VALUE
VALUES (999999, 'test@example.com');

The explicitness is the point—you have to consciously choose to override the system, which means you won’t do it accidentally.

Dropping and Altering Sequences

SERIAL sequences can sometimes be dropped independently of the table, especially if the ownership relationship wasn’t set up correctly:

DROP SEQUENCE users_id_seq;
-- Now your table is broken—inserts fail with "relation does not exist"

Identity columns have a tighter relationship that prevents this:

DROP SEQUENCE users_id_seq;
-- ERROR: cannot drop sequence users_id_seq because column id of table users requires it

Altering sequence properties is also more intuitive with identity columns. With SERIAL, you need to know the sequence name:

-- SERIAL: what's my sequence called again?
ALTER SEQUENCE users_id_seq RESTART WITH 1000;

With identity columns, you work directly with the column:

-- Identity: more intuitive
ALTER TABLE users ALTER COLUMN id RESTART WITH 1000;

Migrating from SERIAL to Identity

If you have existing tables with SERIAL columns, migration is straightforward. First, find all your SERIAL columns:

SELECT
  c.table_name,
  c.column_name,
  c.column_default,
  pg_get_serial_sequence(c.table_schema || '.' || c.table_name, c.column_name) AS sequence_name
FROM information_schema.columns c
WHERE c.column_default LIKE 'nextval%'
  AND c.table_schema = 'public';

Then convert each one:

-- Remove the default (disconnects the sequence)
ALTER TABLE users ALTER COLUMN id DROP DEFAULT;

-- Drop the old sequence
DROP SEQUENCE users_id_seq;

-- Add identity
ALTER TABLE users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

-- Set the sequence to continue from the current max
SELECT setval(
  pg_get_serial_sequence('users', 'id'),
  COALESCE((SELECT MAX(id) FROM users), 0) + 1,
  false
);

The COALESCE here handles the empty-table case so the sequence starts at 1 instead of erroring on NULL. See our guide to COALESCE in SQL for the full pattern around NULL fallbacks.

If you have gaps in your IDs or want to restart numbering from a specific value:

ALTER TABLE users ALTER COLUMN id RESTART WITH 10000;

Sequence Options

Identity columns support all the sequence options you might need:

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY (
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 9999999999
    CACHE 20
  ) PRIMARY KEY
);

The CACHE option is worth knowing about—it pre-allocates a batch of values, which improves insert performance on high-traffic tables. The tradeoff is that if your database restarts, the cached values are lost, creating gaps in your IDs. For most applications, the default cache of 1 is fine.

Which Should You Use?

For new tables, always use identity columns. There’s no reason to use SERIAL in new code:

-- Don't do this
CREATE TABLE users (id SERIAL PRIMARY KEY);

-- Do this instead
CREATE TABLE users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

For existing SERIAL columns, migrate when convenient. It’s not urgent—SERIAL works and will continue to work—but identity columns are better for new team members who won’t understand SERIAL quirks, for CI/CD pipelines that copy schemas, and for permission management.

Use GENERATED ALWAYS unless you have a specific reason for BY DEFAULT. The protection against accidental manual inserts is worth it.

Feature	SERIAL	Identity (ALWAYS)	Identity (BY DEFAULT)
SQL Standard	No	Yes	Yes
Separate sequence permissions	Yes	No	No
Safe schema copying	No	Yes	Yes
Prevents manual inserts	No	Yes	No
PostgreSQL version	All	10+	10+

Wrapping Up

Identity columns are the modern way to create auto-incrementing columns in PostgreSQL. They follow the SQL standard, have better permission handling, and prevent common mistakes with schema copying and manual inserts.

For new projects, always use BIGINT GENERATED ALWAYS AS IDENTITY. For existing projects, migrate when you have the opportunity—the conversion is straightforward and eliminates a class of subtle bugs.