On Tue, Aug 6, 2024 at 10:24 PM Tender Wang <tndrwang@gmail.com> wrote: > PG Bug reporting form <noreply@postgresql.org> 于2024年8月6日周二 15:01写道： >> >> The following bug has been logged on the website: >> >> Bug reference: 18568 >> Logged by: Webbo Han >> Email address: 1105066510@qq.com >> PostgreSQL version: 16.3 >> Operating system: centos 7.6 >> Description: >> >> First, we create one case-insensitive collation use ICU: >> ```sql >> CREATE COLLATION case_insensitive ( >> provider = icu, >> locale = 'und-u-ks-level2', >> deterministic = false >> ); >> ``` >> >> Then, we create the partition table, meanwhile we set the collation of >> column c to `case_insensitive`, >> and set partkey's collation to 'C'. >> ```sql >> SET enable_partitionwise_aggregate TO true; >> SET enable_partitionwise_join TO true; >> SET max_parallel_workers_per_gather TO 0; >> SET enable_incremental_sort TO off; >> CREATE TABLE pagg_tab (c text collate case_insensitive) PARTITION BY LIST(c >> collate "C"); > > > I think above create table again. Should we allow this CREATE TABLE? The partition key > definition are not same with column definiton. Is it better to report error for users?
Not really. As the documentation says, collation can be specified per column or per operation:
In this case, the operation is partitioning. When you specify the COLLATE clause for a partition key, it means that the partitioning logic, such as partition tuple routing, will use that collation instead of the column-specified or the column type's collation.
That's similar to how you can create an index on a column using a different collation than the column's own:
create table foo (a text collate case_insensitive); create index on foo (a collate "C");
Thanks for your explanation.
I do notice that the CREATE TABLE documentation does not describe what the COLLATE clause does when mentioned in a PARTITION BY clause. We'll need to fix that.
