PostgreSQL-13: Partitioning with overlapping ranges

Question

I have a blacklist with certain strings beginning with the character '0','1','2',...,'9','a','b','c','d','e','f','?'.

Then I create the following table:

CREATE TABLE blacklist (concat TEXT UNIQUE NOT NULL) PARTITION BY RANGE (concat);

Now I do RANGE partitioning:

CREATE TABLE IF NOT EXISTS blacklist_list_0 PARTITION OF blacklist FOR VALUES FROM ('0') TO ('0ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_0 ADD CONSTRAINT blacklist_list_0 CHECK (concat >= '0' AND concat <= '0ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_1 PARTITION OF blacklist FOR VALUES FROM ('1') TO ('1ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_1 ADD CONSTRAINT blacklist_list_1 CHECK (concat >= '1' AND concat <= '1ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_2 PARTITION OF blacklist FOR VALUES FROM ('2') TO ('2ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_2 ADD CONSTRAINT blacklist_list_2 CHECK (concat >= '2' AND concat <= '2ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_3 PARTITION OF blacklist FOR VALUES FROM ('3') TO ('3ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_3 ADD CONSTRAINT blacklist_list_3 CHECK (concat >= '3' AND concat <= '3ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_4 PARTITION OF blacklist FOR VALUES FROM ('4') TO ('4ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_4 ADD CONSTRAINT blacklist_list_4 CHECK (concat >= '4' AND concat <= '4ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_5 PARTITION OF blacklist FOR VALUES FROM ('5') TO ('5ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_5 ADD CONSTRAINT blacklist_list_5 CHECK (concat >= '5' AND concat <= '5ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_6 PARTITION OF blacklist FOR VALUES FROM ('6') TO ('6ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_6 ADD CONSTRAINT blacklist_list_6 CHECK (concat >= '6' AND concat <= '6ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_7 PARTITION OF blacklist FOR VALUES FROM ('7') TO ('7ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_7 ADD CONSTRAINT blacklist_list_7 CHECK (concat >= '7' AND concat <= '7ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_8 PARTITION OF blacklist FOR VALUES FROM ('8') TO ('8ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_8 ADD CONSTRAINT blacklist_list_8 CHECK (concat >= '8' AND concat <= '8ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_9 PARTITION OF blacklist FOR VALUES FROM ('9') TO ('9ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_9 ADD CONSTRAINT blacklist_list_9 CHECK (concat >= '9' AND concat <= '9ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_a PARTITION OF blacklist FOR VALUES FROM ('a') TO ('affffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_a ADD CONSTRAINT blacklist_list_a CHECK (concat >= 'a' AND concat <= 'affffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_b PARTITION OF blacklist FOR VALUES FROM ('b') TO ('bffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_b ADD CONSTRAINT blacklist_list_b CHECK (concat >= 'b' AND concat <= 'bffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_c PARTITION OF blacklist FOR VALUES FROM ('c') TO ('cffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_c ADD CONSTRAINT blacklist_list_c CHECK (concat >= 'c' AND concat <= 'cffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_d PARTITION OF blacklist FOR VALUES FROM ('d') TO ('dffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_d ADD CONSTRAINT blacklist_list_d CHECK (concat >= 'd' AND concat <= 'dffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_e PARTITION OF blacklist FOR VALUES FROM ('e') TO ('effffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_e ADD CONSTRAINT blacklist_list_e CHECK (concat >= 'e' AND concat <= 'effffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_f PARTITION OF blacklist FOR VALUES FROM ('f') TO ('fffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
ALTER TABLE blacklist_list_f ADD CONSTRAINT blacklist_list_f CHECK (concat >= 'f' AND concat <= 'fffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );
CREATE TABLE IF NOT EXISTS blacklist_list_rest PARTITION OF blacklist FOR VALUES FROM ('?') TO ('?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
ALTER TABLE blacklist_list_rest ADD CONSTRAINT blacklist_list_rest CHECK (concat >= '?' AND concat <= '?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff' );

I get on Ubuntu 20.04 with postgres 13.1 the following error message:

postgres=# CREATE TABLE IF NOT EXISTS blacklist_list_rest PARTITION OF blacklist FOR VALUES FROM ('?') TO ('?ffffffffffffffffffffffffffffffffffffffffffffffffffffffff');
ERROR:  partition "blacklist_list_rest" would overlap partition "blacklist_list_0"

But I have no problem when I run this on my developer station (gentoo linux, postgres 13.1).

Has someone an idea how to fix this? I don't think these characters overlap and I don't think I missed something like regular expressions ('?' should be detected as a character in this scenario).

Probably collations. Try `concat TEXT UNIQUE NOT NULL collate "C"`. Btw: the check constraints are not needed with declarative partitioning (that was needed with old, deprecated inheritance based partitioning) — , Dec 17 '20 at 17:57
Great, thank you! Although I changed the rest table to DEFAULT, I just tested it and I have a "C" COLLATE as default in gentoo but the Ubuntu testbox has "en_US.UTF-8". After adding COLLATE "C" to the blacklist table everything works. — 0xfb, Dec 17 '20 at 18:13

score 1 · Accepted Answer · answered Dec 17 '20 at 18:16

Postgres uses the collations from the operating system, so comparing and sorting strings might differ on different operating systems (or even on the same system when updates are applied). You can force a comparison with "pure" ASCII without using collations using the "C" collation:

CREATE TABLE blacklist 
(
  concat TEXT UNIQUE NOT NULL collate "C"
)
PARTITION BY RANGE (concat);

PostgreSQL-13: Partitioning with overlapping ranges

1 Answers1