# Identity Columns and RETURNING — PostgreSQL

Source: https://www.skillbyai.com/en/postgresql/t-identity

> Generated ids, returned immediately.

## 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.

```sql
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.

**Quiz:** What does RETURNING do?

- [ ] Undoes the statement
- [x] Returns columns from the rows affected by the statement
- [ ] Returns the table definition
- [ ] Returns the query plan

*Answer:* Returns columns from the rows affected by the statement. Get generated values without another round trip.