# How to Use IDENTITY Columns in PostgreSQL

Source: https://www.bytebase.com/reference/postgres/how-to/how-to-use-identity-column-postgres/

---

_Official documentation: [Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-GENERATED-IDENTITY)_

## 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.

> **Note:** For legacy applications or PostgreSQL versions before 10, you can use [SERIAL columns](/reference/postgres/how-to/how-to-use-serial-postgres) 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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

```sql
-- 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:

```sql
-- 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:

```sql
-- 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](/reference/postgres/how-to/how-to-use-serial-postgres) remain a viable alternative.

## References

- [PostgreSQL Documentation: Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-GENERATED-IDENTITY)
- [PostgreSQL Documentation: ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html)