Using Postgres Identity columns

How to use identity columns in Postgres effectively, including UUID alternatives.

April 10, 2019, updated April 10, 2019

PostgreSQL 10 introduced identity columns as a SQL-standard replacement for serial columns. Combined with UUID support, you have flexible options for primary key generation.

Identity Columns (PostgreSQL 10+)

Identity columns are the modern replacement for SERIAL and BIGSERIAL. They follow the SQL standard and give you better control over how IDs are generated.

Creating an identity column

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

GENERATED ALWAYS AS IDENTITY — PostgreSQL always generates the value; inserting a custom value is an error (unless you use OVERRIDING SYSTEM VALUE)
GENERATED BY DEFAULT AS IDENTITY — PostgreSQL generates a value only when you don’t supply one

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

-- Insert with auto-generated ID
INSERT INTO users (name) VALUES ('Alice');

-- Insert with explicit ID (allowed with BY DEFAULT)
INSERT INTO users (id, name) VALUES (100, 'Bob');

Customizing the sequence

You can control the start value, increment, and cache size:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY (
        START WITH 1
        INCREMENT BY 1
        CACHE 20
    ) PRIMARY KEY,
    name TEXT NOT NULL
);

Identity vs. SERIAL

Why prefer identity columns over SERIAL?

Feature	`SERIAL`	`IDENTITY`
SQL Standard	No	Yes
Prevents manual inserts	No	Yes (`ALWAYS`)
Sequence tied to column	No (separate object)	Yes (owned by column)
Dropped with column	Not automatically	Yes

-- Old way (SERIAL) - sequence is separate
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

-- New way (IDENTITY) - sequence is owned by the column
CREATE TABLE users (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT);

Removing an identity column property

To remove the identity property from a column without dropping the column:

ALTER TABLE users ALTER COLUMN id DROP IDENTITY;

To add identity to an existing column:

ALTER TABLE users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;

Using UUIDs as Primary Keys

If you need globally unique IDs (e.g., for distributed systems or to avoid exposing sequential IDs), use UUIDs instead of integers.

Enable the uuid-ossp extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

UUID as default

CREATE TABLE users (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    name TEXT NOT NULL
);

UUID with gen_random_uuid() (PostgreSQL 13+)

PostgreSQL 13+ has a built-in gen_random_uuid() function:

CREATE TABLE users (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL
);

Using UUIDs with JPA / Hibernate

import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;

@Entity
public class User {

    @Id
    @GeneratedValue(generator = "UUID")
    @GenericGenerator(name = "UUID", strategy = "org.hibernate.id.UUIDGenerator")
    @Column(updatable = false, nullable = false)
    private UUID id;

    private String name;

    // getters and setters
}

Which Should You Use?

Use Case	Best Choice
Simple auto-incrementing IDs	`IDENTITY` column
Need to insert custom IDs sometimes	`GENERATED BY DEFAULT AS IDENTITY`
Distributed systems, hide creation order	UUID
URL-safe, short IDs	Consider `nanoid` or a custom scheme