SERIAL works with NULL, GENERATED ALWAYS AS IDENTITY not

Question

Postgres 12:

CREATE TABLE l_table (
    id INT generated always as identity,
    w_id int NOT null references w_table(id),
    primary key (w_id, id)
)PARTITION BY LIST (w_id);

CREATE table l1 PARTITION OF l_table FOR VALUES IN (1);

insert into l1 (w_id) values (1);

I'm getting:

ERROR: null value in column "id" violates not-null constraint

If I replace INT generated always as identity with SERIAL it works. This is odd as in another table the generated always as identity works with null. Using default as value does not work either.

GAAI is supposed to be the SQL standard way of replacing SERIAL, even It's the suggested one. What am I missing here?

Answer 1

What am I missing here?

You're trying to insert into the partition table l1 directly, instead of the partitioned l_table. This ignores the identity column on the parent table, tries to insert the default null, and fails the non-null constraint that every identity column has. If you instead do

insert into l_table (w_id) values (1);

it will work and route the inserted row into the right partition.

Using default as value does not work either.

Apparently it's quite hard to do that. How to DEFAULT Partitioned Identity Column? over at dba.SE discusses some workarounds.