PostgreSQL Identity Column

Summary: in this tutorial, you’ll learn how to use the PostgreSQL identity column that automatically generates unique integers using an implicit sequence.

Getting Started with the PostgreSQL Identity Column #

In PostgreSQL, the identity column is a special feature that brings convenience by automatically generating unique integers using an implicit sequence.

To define an identity column, you use one of two the following constraints:

GENERATED ALWAYS AS IDENTITY: This ensures the column always uses generated integer numbers. If a column has this constraint, you cannot provide an explicit value in the insert or update statement, except if you use the OVERRIDING SYSTEM VALUE in the INSERT statement.
GENERATED BY DEFAULT AS IDENTITY: This constraint, similar to the above, offers flexibility by allowing you to provide an explicit integer for insertion.

It’s typical to use the identity column as the primary key column of a table, as it automatically generates unique integers, simplifying the management of primary keys.

The following statement shows how to define a column as an identity column as a primary key column:

CREATE TABLE table_name (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  column1 data_type,
  ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Besides the INT data type, you can use the SMALLINT and BIGINT data types.

Since the id column uses the GENERATED AS ALWAYS IDENTITY constraint, you cannot provide an id when inserting a row into the table.

The following statement will fail:

INSERT INTO
  table_name (id, column1)
VALUES
  (1, value1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

But if you have a good reason to use an explicit id value, the OVERRIDING SYSTEM VALUE clause in the INSERT statement gives you the control:

INSERT INTO
  table_name (id, column1) 
OVERRIDING SYSTEM VALUE
VALUES
  (1, value1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the OVERRIDING SYSTEM VALUE clause allows you to insert value one into the id column explicitly.

The following shows how to define an identity column that uses the GENERATED BY DEFAULT AS IDENTITY constraint:

CREATE TABLE table_name (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    column1 data_type,
    ...
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this table, you can let the sequence generate unique integers or explicitly provide an integer in the INSERT statement:

INSERT INTO table_name(id, column1)
VALUES(1, value1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL identity column as the primary key column #

First, create a table sales_orders that stores sales order headers:

CREATE TABLE sales_orders(
    order_no INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    status VARCHAR(25) NOT NULL,
    requested_date DATE NOT NULL,
    created_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The id column of the sales_orders table is an identity column.

Second, insert two rows into the sales_orders table:

INSERT INTO
  sales_orders (customer_name, status, requested_date)
VALUES
  ('John Doe', 'Pending', '2024-12-15'),
  ('Jane Doe', 'Completed', '2024-12-20') 
RETURNING order_no;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no
----------
        1
        2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the order_no is an identity column, PostgreSQL automatically generates unique integers.

The id column of the sales_orders table uses the GENERATED ALWAYS AS IDENTITY constraint. Therefore, it will not accept an implicit value.

Third, attempt to insert a new row into the table with an implicit id:

INSERT INTO sales_orders (order_no , customer_name, status, requested_date)
VALUES(3, 'Adam Smith', 'Pending', '2024-12-17');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Error:

Column "order_no" is an identity column defined as GENERATED ALWAYS.Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To insert a value into the GENERATED ALWAYS AS IDENTITY column, you can use the OVERRIDING SYSTEM VALUE clause in the insert statement:

INSERT INTO sales_orders (order_no , customer_name, status, requested_date)
OVERRIDING SYSTEM VALUE
VALUES(3, 'Adam Smith', 'Pending', '2024-12-17')
RETURNING order_no;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no
----------
        3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL identity column as a non-primary key column #

First, create a new table called sales_order_items that stores the items of sales orders:

CREATE TABLE sales_order_items (
  order_no INT,
  item_no INT GENERATED BY DEFAULT AS IDENTITY UNIQUE,
  product_name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  price DEC(15, 2) NOT NULL CHECK (price > 0),
  PRIMARY KEY (order_no, item_no),
  FOREIGN KEY (order_no) REFERENCES sales_orders (order_no) ON DELETE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, add two items to the order #1:

INSERT INTO
  sales_order_items (order_no, product_name, quantity, price)
VALUES
  (1, 'iPhone 16 Pro', 5, 1299.99),
  (1, 'Screen Protector', 5, 9.99) 
RETURNING order_no, item_no;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no | item_no
----------+---------
        1 |       1
        1 |       2Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, add a new item into to the sales order one but provide the item_no value explicitly:

INSERT INTO
  sales_order_items (order_no, item_no, product_name, quantity, price)
VALUES
  (1, 3, 'Charger', 5, 29.99)
RETURNING order_no, item_no;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 order_no | item_no
----------+---------
        1 |       3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Since the item_no column uses the GENERATED BY DEFAULT AS IDENTITY constraint, we can explicitly insert an integer into it.

Implicit Sequences #

When you create an identity column for a table, PostgreSQL automatically generates an implicit sequence and associates it with the column.

The sequence will have the following naming convention:

<table_name>_<column_name>_seqCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, PostgreSQL creates the following sequence for the order_no column of the sales_orders table:

sales_orders_order_no_seqCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Similarly, it creates the following sequence for the item_no column of the sales_order_items table:

sales_order_items_item_no_seqCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL allows you to configure the sequence when defining an identity column.

For example, the following creates a table sales_order_item_schedules that stores the scheduling of delivery dates and quantities for each line item in a sales order.

CREATE TABLE sales_order_item_schedules (
  item_no INT,
  schedule_no INT GENERATED BY DEFAULT AS IDENTITY (INCREMENT 10 START 10),
  delivery_qty INT NOT NULL,
  delivered_on DATE NOT NULL,
  PRIMARY KEY (item_no, schedule_no),
  FOREIGN KEY (item_no)  
      REFERENCES sales_order_items (item_no) 
      ON DELETE CASCADE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The schedule_no column is an identity column. Additionally, we configure the underlying sequence so the number starts at ten and increments by ten for each sales order line item.

The following statement inserts three rows into the sales_order_item_schedules table:

INSERT INTO
  sales_order_item_schedules (item_no, delivery_qty, delivered_on)
VALUES
  (1, 1, '2024-12-16'),
  (1, 2, '2024-12-17'),
  (1, 2, '2024-12-17') 
RETURNING item_no, schedule_no ;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 item_no | schedule_no
---------+-------------
       1 |          10
       1 |          20
       1 |          30Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The output indicates that the values in the schedule_no column start at ten and increment by for each row.

Configuring an existing column as an identity column #

To change a column to an identity column, you can use the ALTER TABLE statement:

ALTER TABLE table_name
ALTER COLUMN column_name
ADD GENERATED { ALWAYS | BY DEFAULT } 
AS IDENTITY 
[sequence_option];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The column_name must have a NOT NULLconstraint or you’ll encounter an error.

Let’s take an example:

First, create a table taxes to store sales taxes:

CREATE TABLE taxes (
   id INT PRIMARY KEY, 
   rate DECIMAL(5, 2) NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, set the id column as an identity column:

ALTER TABLE taxes
ALTER COLUMN id 
ADD GENERATED ALWAYS AS IDENTITY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, insert some rows into the taxes table:

INSERT INTO
  taxes (rate)
values
  (0.05),
  (0.08) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | rate
----+------
  1 | 0.05
  2 | 0.08Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Setting identity column characteristics #

PostgreSQL allows you to configure the characteristics of an identity column:

Switching between the GENERATED ALWAYS and GENERATED BY DEFAULT.
Change the parameters for the implicit sequence.

Here’s the syntax:

ALTER TABLE table_name
ALTER COLUMN column_name
SET GENERATED { ALWAYS | BY DEFAULT}
[SET sequence_option]Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example:

First, change the id column of the taxes table from GENERATED ALWAYS to GENERATED BY DEFAULT:

ALTER TABLE taxes
ALTER COLUMN id
SET GENERATED BY DEFAULT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert an explicit integer value into the id column of the taxes table:

INSERT INTO taxes(id, rate)
VALUES(3, 0.1)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 id | rate
----+------
  3 | 0.10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, set the increment value by 5, instead of using 1 by default:

ALTER TABLE taxes
ALTER COLUMN id
SET INCREMENT BY 5;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, insert a new row into the taxes table:

INSERT INTO taxes(rate)
VALUES(0.12)
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The id value is seven instead, not 8 (3 + 5), because the sequence did not generate the number 3. It returns the next sequence value, 7 ( 2 + 5 ).

Removing GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY constraints #

To drop a GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY constraint from a column, you use the ALTER TABLE DROP IDENTITY statement:

ALTER TABLE table_name
ALTER COLUMN column_name
DROP IDENTITY [ IF EXISTS ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The IF EXISTS clause conditionally drops the identity constraint only if it exists.

For example, the following statement removes the GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY constraint from the id column of the taxes table:

ALTER TABLE taxes
ALTER COLUMN id
DROP IDENTITY IF EXISTS;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Summary #

Use the identity column to generate unique integers using an implicit sequence.
Set up an identity column using the GENERATED AWAYS AS IDENTITY or GENERATED BY DEFAULT AS DEFAULT constraint.

Quiz #

Was this tutorial helpful ?