How to Use Identity Column in PostgreSQL: Syntax and Examples

In PostgreSQL, use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY to create an identity column that auto-increments unique values. This replaces older serial types and is SQL standard compliant, ensuring automatic number generation for new rows.

Syntax

The identity column in PostgreSQL is defined using the GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY clause in a CREATE TABLE or ALTER TABLE statement.

• ALWAYS: PostgreSQL always generates a value for the column, ignoring any user input.
• BY DEFAULT: PostgreSQL generates a value only if no value is provided during insert.
• The identity column must be of an integer type like INT or BIGINT.

sql

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column_name data_type
);

Example

This example creates a table with an identity column that auto-increments on each new row insert. It shows how to insert rows without specifying the identity column and how PostgreSQL generates the values automatically.

sql

CREATE TABLE users (
  user_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username TEXT NOT NULL
);

INSERT INTO users (username) VALUES ('alice');
INSERT INTO users (username) VALUES ('bob');

SELECT * FROM users;

Output

 user_id | username 
---------+----------
       1 | alice
       2 | bob
(2 rows)

Common Pitfalls

Common mistakes when using identity columns include:

• Trying to insert explicit values into an ALWAYS identity column, which causes errors.
• Confusing SERIAL with identity columns; identity is preferred as it is SQL standard and more flexible.
• Not specifying the identity clause correctly, leading to no auto-increment behavior.

Example of wrong and right usage:

sql

-- Wrong: inserting explicit value into ALWAYS identity column causes error
CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT
);

-- This will fail:
-- INSERT INTO products (product_id, name) VALUES (10, 'item');

-- Right: use BY DEFAULT if you want to allow explicit values
CREATE TABLE products2 (
  product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name TEXT
);

INSERT INTO products2 (product_id, name) VALUES (10, 'item'); -- works
INSERT INTO products2 (name) VALUES ('item2'); -- auto-generated id

Quick Reference

Feature	Description
GENERATED ALWAYS AS IDENTITY	PostgreSQL always generates the value; user cannot insert explicit values.
GENERATED BY DEFAULT AS IDENTITY	PostgreSQL generates value if none is provided; user can insert explicit values.
Data Types	Use integer types like INT, BIGINT for identity columns.
Replacement	Preferred over SERIAL for auto-incrementing columns.
Error on explicit insert	Occurs only with GENERATED ALWAYS identity columns.

Key Takeaways

Use GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY to create auto-incrementing columns in PostgreSQL.

GENERATED ALWAYS disallows explicit inserts; BY DEFAULT allows them.

Identity columns replace the older SERIAL type and follow SQL standards.

Always define identity columns with integer data types like INT or BIGINT.

Avoid inserting explicit values into ALWAYS identity columns to prevent errors.