Identity Columns

Created at: 2025-04-15

Identity columns can be created with the following statements:

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
    id1 SERIAL PRIMARY KEY,
    id2 INT GENERATED ALWAYS AS IDENTITY,
    id3 BIGINT GENERATED BY DEFAULT AS IDENTITY,
    val INT NOT NULL
);

The identity column will be back-up (behind the scenes) by a sequence. For example, you can find the underlying sequence in this way:

SELECT
  *
FROM
  pg_sequences
WHERE
  sequencename IN (
      split_part(pg_get_serial_sequence('foo', 'id2'), '.', 2),
      split_part(pg_get_serial_sequence('foo', 'id3'), '.', 2)
  );

-- -[ RECORD 1 ]-+--------------------
-- schemaname    | public
-- sequencename  | foo_id2_seq
-- sequenceowner | username
-- data_type     | integer
-- start_value   | 1
-- min_value     | 1
-- max_value     | 2147483647
-- increment_by  | 1
-- cycle         | f
-- cache_size    | 1
-- last_value    |
-- -[ RECORD 2 ]-+--------------------
-- schemaname    | public
-- sequencename  | foo_id3_seq
-- sequenceowner | username
-- data_type     | bigint
-- start_value   | 1
-- min_value     | 1
-- max_value     | 9223372036854775807
-- increment_by  | 1
-- cycle         | f
-- cache_size    | 1
-- last_value    |

The sequence max_value and data_type are defined implicitly by the column data type. That's why the bigint (id3) column has a much larger max_value.

ALWAYS vs BY DEFAULT

The clauses ALWAYS and BY DEFAULT in the column definition determine how explicitly user-specified values are handled in INSERT and UPDATE commands. In an INSERT command, if ALWAYS is selected, a user-specified value is only accepted if the INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is selected, then the user-specified value takes precedence. Thus, using BY DEFAULT results in a behavior more similar to default values, where the default value can be overridden by an explicit value, whereas ALWAYS provides some more protection against accidentally inserting an explicit value.

For our table, the following would work:

-- All the id* columns will be populated automatically.
INSERT INTO foo (val) VALUES (1);

-- This also works, because the value id3 was defined with BY DEFAULT and can
-- therefore be overriden.
-- NOTE: this does not update the sequence to now have its last_value updated.
-- This can be dangerous if the column is a Primary Key.
INSERT INTO foo (id3, val) VALUES (42, 1);

-- This also works, and updates the sequences too
INSERT INTO foo (id2, id3, val) VALUES (DEFAULT, DEFAULT, 1);

-- This works because of the OVERRIDING SYSTEM VALUE command. But it will not
-- update the sequence last_value.
INSERT INTO foo (id2, id3, val) OVERRIDING SYSTEM VALUE VALUES (42, 64, 1);

The following would not work:

INSERT INTO foo (id2, id3, val) VALUES (42, DEFAULT, 1);
-- ERROR:  cannot insert a non-DEFAULT value into column "id2"
-- DETAIL:  Column "id2" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

This is what our table looks like after the experiments:

select * from foo;
--  id1 | id2 | id3 | val
-- -----+-----+-----+-----
--    1 |   1 |   1 |   1
--    2 |   2 |  42 |   1
--    3 |   3 |   2 |   1
--    5 |  42 |  64 |   1

-- schemaname    | public
-- sequencename  | foo_id2_seq
-- sequenceowner | username
-- data_type     | integer
-- start_value   | 1
-- min_value     | 1
-- max_value     | 2147483647
-- increment_by  | 1
-- cycle         | f
-- cache_size    | 1
-- last_value    | 4
-- -[ RECORD 2 ]-+--------------------
-- schemaname    | public
-- sequencename  | foo_id3_seq
-- sequenceowner | username
-- data_type     | bigint
-- start_value   | 1
-- min_value     | 1
-- max_value     | 9223372036854775807
-- increment_by  | 1
-- cycle         | f
-- cache_size    | 1
-- last_value    | 3

Note that the underlying sequences can be updated just as a normal sequence.