Hey PG community,
PG version: 13
Platform: Linux
I was wondering if anyone understands why there is the
restriction/limitation on using expressions (on a primary/unique key)
as part of a table partition definition. E.g.
CREATE TABLE foobar(
id BIGINT NOT NULL PRIMARY KEY,
baz VARCHAR NULL DEFAULT NULL
) PARTITION BY HASH(my_func(id));
Error: primary key constraints cannot be used when partition keys
include expressions.
I have a case where using either hash or list partitioning schemes,
it's handy to use my_func() on the chosen field (look up some
accompanying value in another table, for example) but I cannot because
it violates a limitation imposed by PG. Yet, I very much want 'id' to
be my primary key! What's more, to retain referential integrity I want
to keep 'id' as a primary key because in some other relation I define
a foreign key by it.
I couldn't find much, if anything, about using expressions in table
partitions let alone describing the restriction. Can anyone enlighten
me? Or point me to what I've missed! Also, is there a chance that this
limitation will be relaxed in the future?
Here's an example on dbfiddle;
https://dbfiddle.uk/?rdbms=postgres_13&fiddle=cb498f4e3c6b06e1b61bb0b7e57747e6
Note the use of ON CONFLICT is key to our code and so attempting any
dynamic creations of individual child partitions, indices etc. also
fail to work properly since the unique id constraint isn't known to
the parent table.
Cheers
Jim
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London