Re: Restriction on table partition expressions

On Fri, 26 Aug 2022 at 03:08, James Vanns <jvanns(at)ilm(dot)com> wrote:
> 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 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?

This is mentioned in [1]:

"Unique constraints (and hence primary keys) on partitioned tables
must include all the partition key columns. This limitation exists
because the individual indexes making up the constraint can only
directly enforce uniqueness within their own partitions; therefore,
the partition structure itself must guarantee that there are not
duplicates in different partitions."

Maybe that could be more clear and mention that all the primary key
columns must be present and not be part of a function call or
expression.

David

[1] https://www.postgresql.org/docs/13/ddl-partitioning.html