Identity Columns and RETURNING

GENERATED ALWAYS AS IDENTITY

Identity columns (GENERATED ALWAYS AS IDENTITY) are the standard way to generate integer ids, replacing the older serial. With ALWAYS, the database refuses manually supplied ids unless you explicitly override, preventing collisions with the sequence. RETURNING sends back columns from inserted, updated or deleted rows in the same statement, so applications get generated ids and defaults without a second query.

Insert with RETURNING, then a forbidden manual id, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. The insert returns the generated id 5 and the NULL city. Supplying id 99 manually is refused because the column is GENERATED ALWAYS, with a hint about OVERRIDING SYSTEM VALUE.

INSERT INTO customers (name, email) VALUES ('Zara', 'zara@example.com')
RETURNING id, name, city;
INSERT INTO customers (id, name, email) VALUES (99, 'Manual', 'm@example.com');

Output:

 id | name | city 
----+------+------
  5 | Zara | 
(1 row)

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Prefer bigint ids

Integer ids can run out on busy tables; bigint costs a few bytes and avoids a painful migration later.

त्वरित जाँच: What does RETURNING do?

• Undoes the statement
• Returns columns from the rows affected by the statement
• Returns the table definition
• Returns the query plan