How to Use IDENTITY Columns in PostgreSQL

Official documentation: Identity Columns

What are IDENTITY Columns?#

IDENTITY columns, introduced in PostgreSQL 10, provide a SQL standard-compliant way to create auto-incrementing columns. They offer better control and portability compared to SERIAL, following the SQL:2003 standard.

For legacy applications or PostgreSQL versions before 10, you can use SERIAL columns as an alternative approach.

IDENTITY vs SERIAL#

Feature	IDENTITY	SERIAL
SQL Standard	SQL:2003 compliant	PostgreSQL-specific
PostgreSQL Version	10+	All versions
Control	Better control options	Limited control
Portability	More portable	PostgreSQL only

IDENTITY Generation Modes#

IDENTITY columns support two generation modes:

GENERATED BY DEFAULT: Values are generated by default, but can be overridden
GENERATED ALWAYS: Values are always generated automatically (strict mode)

Creating Tables with IDENTITY#

Basic Usage#

-- GENERATED BY DEFAULT (recommended for most cases)
CREATE TABLE products (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(10,2)
);

-- GENERATED ALWAYS (strict mode)
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INTEGER,
    order_date TIMESTAMPTZ DEFAULT NOW()
);

Custom Options#

-- IDENTITY with custom sequence options
CREATE TABLE customers (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY (
        START WITH 1000
        INCREMENT BY 1
        MINVALUE 1000
        MAXVALUE 999999
        CACHE 10
    ) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

Working with IDENTITY Columns#

Inserting Data#

-- GENERATED BY DEFAULT: Auto-generate ID
INSERT INTO products (name, price)
VALUES ('Widget', 19.99)
RETURNING id;

-- GENERATED BY DEFAULT: Override with explicit value
INSERT INTO products (id, name, price)
VALUES (5000, 'Special Widget', 29.99);

-- GENERATED ALWAYS: Cannot override (will error)
INSERT INTO orders (customer_id) VALUES (123);

-- GENERATED ALWAYS: Force override with OVERRIDING SYSTEM VALUE
INSERT INTO orders (id, customer_id)
OVERRIDING SYSTEM VALUE
VALUES (9999, 456);

Managing Sequences#

-- View sequence information
SELECT * FROM pg_sequences WHERE sequencename LIKE '%_id_seq';

-- Restart sequence from a specific value
ALTER TABLE products ALTER COLUMN id RESTART WITH 2000;

-- Change sequence options
ALTER TABLE products ALTER COLUMN id SET INCREMENT BY 10;

-- Set multiple sequence options
ALTER TABLE products ALTER COLUMN id SET (
    START WITH 1000,
    INCREMENT BY 5,
    CACHE 20
);

Migration Between SERIAL and IDENTITY#

From SERIAL to IDENTITY#

-- Remove SERIAL default and add IDENTITY
ALTER TABLE old_products ALTER COLUMN id DROP DEFAULT;
ALTER TABLE old_products ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

-- Drop the old sequence (optional)
DROP SEQUENCE IF EXISTS old_products_id_seq;

From IDENTITY to SERIAL#

-- Remove IDENTITY and create sequence
ALTER TABLE products ALTER COLUMN id DROP IDENTITY;

CREATE SEQUENCE products_id_seq;
ALTER TABLE products ALTER COLUMN id SET DEFAULT nextval('products_id_seq');
ALTER SEQUENCE products_id_seq OWNED BY products.id;

-- Set sequence to current max value
SELECT setval('products_id_seq', (SELECT MAX(id) FROM products));

Best Practices#

Choose the Right Mode#

GENERATED BY DEFAULT: Most use cases where you occasionally need specific IDs
GENERATED ALWAYS: When you want strict control and never allow manual IDs

Data Type Selection#

Choose based on expected table size:

SMALLINT: < 32K rows (2 bytes)
INTEGER: < 2B rows (4 bytes)
BIGINT: > 2B rows (8 bytes)

After Data Import#

Always sync the sequence after importing data:

-- Method 1: Using ALTER TABLE
ALTER TABLE products
    ALTER COLUMN id RESTART WITH (SELECT MAX(id) + 1 FROM products);

-- Method 2: Using setval
SELECT setval(pg_get_serial_sequence('products', 'id'),
              (SELECT MAX(id) FROM products));

Performance Optimization#

For high-throughput applications:

-- Increase cache size
ALTER TABLE products ALTER COLUMN id SET (CACHE 100);

-- For bulk operations, temporarily switch to BY DEFAULT
ALTER TABLE orders ALTER COLUMN id SET GENERATED BY DEFAULT;
-- Switch back after bulk operation
ALTER TABLE orders ALTER COLUMN id SET GENERATED ALWAYS;

Summary#

IDENTITY columns provide a modern, SQL standard-compliant way to create auto-incrementing columns in PostgreSQL:

Use for new PostgreSQL 10+ applications
Choose BY DEFAULT for flexibility, ALWAYS for strict control
Better portability and control than SERIAL
Always sync sequences after data imports

For older PostgreSQL versions, SERIAL columns remain a viable alternative.