Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Tender Wang <tndrwang(at)gmail(dot)com> 于2024年10月23日周三 21:48写道：

> Hi all,
>
> I find another issue as $SUBJECT when I work on [1].
>

When I continue to work on this, I find below issue. But I'm not sure
whether it is a bug.

postgres=# create table part_index(a text primary key) partition by list (
a collate "POSIX");
ERROR: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.
postgres=# create table part_index(a text) partition by list ( a collate
"POSIX");
CREATE TABLE
postgres=# alter table part_index add primary key (a);
ERROR: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.

It seems we can't create a primary key if the collation is different
between columnDef and PartitionKey.

By the way, I think the error message is misleading to users.
ostgres=# alter table part_index add primary key (a);
ERROR: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
which is part of the partition key.

--
Thanks,
Tender Wang