PostgreSQL identity columns for auto-generated keys
Short answer
Use id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or BY DEFAULT) for auto-numbered keys. Values come from an implicit sequence when you insert without an id. [S1]
ALWAYS vs BY DEFAULT
These clauses set how a user-supplied id is handled: ALWAYS refuses it unless you override, BY DEFAULT accepts it. [S1][S2] Prefer ALWAYS to keep keys consistent.
Claims
- id: C1 claim: Identity columns are generated from an implicit sequence. source: S1 evidence: "generated automatically from an implicit sequence"
- id: C2 claim: Identity columns are created with GENERATED AS IDENTITY. source: S1 evidence: "use the GENERATED ... AS IDENTITY clause in CREATE TABLE"
- id: C3 claim: ALWAYS and BY DEFAULT control user-supplied values. source: S1 evidence: "determine how explicitly user-specified values are handled"
- id: C4 claim: CREATE TABLE defines a new table. source: S2 evidence: "CREATE TABLE — define a new table"
Sources
- [S1] PostgreSQL: Documentation: 18: 5.3. Identity Columns. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/ddl-identity-columns.html . License: PostgreSQL License. Accessed 2026-11-04.
- [S2] PostgreSQL: Documentation: 18: CREATE TABLE. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/sql-createtable.html . License: PostgreSQL License. Accessed 2026-11-04.
License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).
License: CC-BY-4.0 · Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 5.3. Identity Columns, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: CREATE TABLE, PostgreSQL License.
Cite this
iinventedlight. 2026. "PostgreSQL identity columns for auto-generated keys." iinventedlight Library, iil:note:8epj0299, version of 2026-11-04. https://iinventedlight.com/library/notes/postgres-identity-columns/. License: CC-BY-4.0.
@misc{iil_note_8epj0299,
author = {{iinventedlight}},
title = {{PostgreSQL identity columns for auto-generated keys}},
howpublished = {iinventedlight Library},
year = {2026},
date = {2026-11-04},
url = {https://iinventedlight.com/library/notes/postgres-identity-columns/},
note = {Permanent ID iil:note:8epj0299. License: CC-BY-4.0.},
}Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.
For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-identity-columns.md.