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