| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 95 kB |
|
Quelle collate.icu.utf8.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] /*
* This test is for ICU collations. */ /* skip test if not UTF8 server encoding or no ICU collations installed */ SELECT getdatabaseencoding() <> 'UTF8' OR (SELECT count(*) FROM pg_collation WHERE collprovider = 'i' AND collname <> 'unicode') = 0 AS skip_test \gset \if :skip_test \quit \endif SET client_encoding TO UTF8; CREATE SCHEMA collate_tests; SET search_path = collate_tests; CREATE TABLE collate_test1 ( a int, b text COLLATE "en-x-icu" NOT NULL ); \d collate_test1 Table "collate_tests.collate_test1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | text | en-x-icu | not null | CREATE TABLE collate_test_fail ( a int, b text COLLATE "ja_JP.eucjp-x-icu" ); ERROR: collation "ja_JP.eucjp-x-icu" for encoding "UTF8" does not exist LINE 3: b text COLLATE "ja_JP.eucjp-x-icu" ^ CREATE TABLE collate_test_fail ( a int, b text COLLATE "foo-x-icu" ); ERROR: collation "foo-x-icu" for encoding "UTF8" does not exist LINE 3: b text COLLATE "foo-x-icu" ^ CREATE TABLE collate_test_fail ( a int COLLATE "en-x-icu", b text ); ERROR: collations are not supported by type integer LINE 2: a int COLLATE "en-x-icu", ^ CREATE TABLE collate_test_like ( LIKE collate_test1 ); \d collate_test_like Table "collate_tests.collate_test_like" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | b | text | en-x-icu | not null | CREATE TABLE collate_test2 ( a int, b text COLLATE "sv-x-icu" ); CREATE TABLE collate_test3 ( a int, b text COLLATE "C" ); INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'äbc'), (3, 'bbc'), (4, 'ABC'); INSERT INTO collate_test2 SELECT * FROM collate_test1; INSERT INTO collate_test3 SELECT * FROM collate_test1; SELECT * FROM collate_test1 WHERE b >= 'bbc'; a | b ---+----- 3 | bbc (1 row) SELECT * FROM collate_test2 WHERE b >= 'bbc'; a | b ---+----- 2 | äbc 3 | bbc (2 rows) SELECT * FROM collate_test3 WHERE b >= 'bbc'; a | b ---+----- 2 | äbc 3 | bbc (2 rows) SELECT * FROM collate_test3 WHERE b >= 'BBC'; a | b ---+----- 1 | abc 2 | äbc 3 | bbc (3 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; a | b ---+----- 2 | äbc 3 | bbc (2 rows) SELECT * FROM collate_test1 WHERE b >= 'bbc' COLLATE "C"; a | b ---+----- 2 | äbc 3 | bbc (2 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "C"; a | b ---+----- 2 | äbc 3 | bbc (2 rows) SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "en-x-icu"; ERROR: collation mismatch between explicit collations "C" and "en-x-icu" LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "e... ^ CREATE DOMAIN testdomain_sv AS text COLLATE "sv-x-icu"; CREATE DOMAIN testdomain_i AS int COLLATE "sv-x-icu"; -- fails ERROR: collations are not supported by type integer LINE 1: CREATE DOMAIN testdomain_i AS int COLLATE "sv-x-icu"; ^ CREATE TABLE collate_test4 ( a int, b testdomain_sv ); INSERT INTO collate_test4 SELECT * FROM collate_test1; SELECT a, b FROM collate_test4 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) CREATE TABLE collate_test5 ( a int, b testdomain_sv COLLATE "en-x-icu" ); INSERT INTO collate_test5 SELECT * FROM collate_test1; SELECT a, b FROM collate_test5 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, b FROM collate_test1 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, b FROM collate_test2 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, b FROM collate_test3 ORDER BY b; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) -- star expansion SELECT * FROM collate_test1 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT * FROM collate_test2 ORDER BY b; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT * FROM collate_test3 ORDER BY b; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) -- constant expression folding SELECT 'bbc' COLLATE "en-x-icu" > 'äbc' COLLATE "en-x-icu" AS "true"; true ------ t (1 row) SELECT 'bbc' COLLATE "sv-x-icu" > 'äbc' COLLATE "sv-x-icu" AS "false"; false ------- f (1 row) -- upper/lower CREATE TABLE collate_test10 ( a int, x text COLLATE "en-x-icu", y text COLLATE "tr-x-icu" ); INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_tes a | lower | lower | upper | upper | initcap | initcap ---+-------+-------+-------+-------+---------+--------- 1 | hij | hij | HIJ | HİJ | Hij | Hij 2 | hij | hıj | HIJ | HIJ | Hij | Hıj (2 rows) SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10; a | lower | lower ---+-------+------- 1 | hij | hij 2 | hij | hij (2 rows) SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; a | x | y ---+-----+----- 2 | HIJ | HIJ 1 | hij | hij (2 rows) SELECT lower('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' COLLATE "en-x-icu"); lower ------------------------------- abcd 123 #$% ıiii̇ ß ß dždždž σσς (1 row) SELECT casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' COLLATE "en-x-icu"); casefold --------------------------------- abcd 123 #$% ıiii̇ ss ss dždždž σσσ (1 row) SELECT lower('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' COLLATE "tr-x-icu"); lower ------------------------------- abcd 123 #$% ıiıi ß ß dždždž σσς (1 row) SELECT casefold('AbCd 123 #$% ıiIİ ẞ ß DŽDždž Σσς' COLLATE "tr-x-icu"); casefold --------------------------------- abcd 123 #$% ıiıi ss ss dždždž σσσ (1 row) -- LIKE/ILIKE SELECT * FROM collate_test1 WHERE b LIKE 'abc'; a | b ---+----- 1 | abc (1 row) SELECT * FROM collate_test1 WHERE b LIKE 'abc%'; a | b ---+----- 1 | abc (1 row) SELECT * FROM collate_test1 WHERE b LIKE '%bc%'; a | b ---+----- 1 | abc 2 | äbc 3 | bbc (3 rows) SELECT * FROM collate_test1 WHERE b ILIKE 'abc'; a | b ---+----- 1 | abc 4 | ABC (2 rows) SELECT * FROM collate_test1 WHERE b ILIKE 'abc%'; a | b ---+----- 1 | abc 4 | ABC (2 rows) SELECT * FROM collate_test1 WHERE b ILIKE '%bc%'; a | b ---+----- 1 | abc 2 | äbc 3 | bbc 4 | ABC (4 rows) SELECT 'Türkiye' COLLATE "en-x-icu" ILIKE '%KI%' AS "true"; true ------ t (1 row) SELECT 'Türkiye' COLLATE "tr-x-icu" ILIKE '%KI%' AS "false"; false ------- f (1 row) SELECT 'bıt' ILIKE 'BIT' COLLATE "en-x-icu" AS "false"; false ------- f (1 row) SELECT 'bıt' ILIKE 'BIT' COLLATE "tr-x-icu" AS "true"; true ------ t (1 row) -- The following actually exercises the selectivity estimation for ILIKE. SELECT relname FROM pg_class WHERE relname ILIKE 'abc%'; relname --------- (0 rows) -- regular expressions SELECT * FROM collate_test1 WHERE b ~ '^abc$'; a | b ---+----- 1 | abc (1 row) SELECT * FROM collate_test1 WHERE b ~ '^abc'; a | b ---+----- 1 | abc (1 row) SELECT * FROM collate_test1 WHERE b ~ 'bc'; a | b ---+----- 1 | abc 2 | äbc 3 | bbc (3 rows) SELECT * FROM collate_test1 WHERE b ~* '^abc$'; a | b ---+----- 1 | abc 4 | ABC (2 rows) SELECT * FROM collate_test1 WHERE b ~* '^abc'; a | b ---+----- 1 | abc 4 | ABC (2 rows) SELECT * FROM collate_test1 WHERE b ~* 'bc'; a | b ---+----- 1 | abc 2 | äbc 3 | bbc 4 | ABC (4 rows) CREATE TABLE collate_test6 ( a int, b text COLLATE "en-x-icu" ); INSERT INTO collate_test6 VALUES (1, 'abc'), (2, 'ABC'), (3, '123'), (4, 'ab1'), (5, 'a1!'), (6, 'a c'), (7, '!.;'), (8, ' '), (9, 'äbç'), (10, 'ÄBÇ'); SELECT b, b ~ '^[[:alpha:]]+$' AS is_alpha, b ~ '^[[:upper:]]+$' AS is_upper, b ~ '^[[:lower:]]+$' AS is_lower, b ~ '^[[:digit:]]+$' AS is_digit, b ~ '^[[:alnum:]]+$' AS is_alnum, b ~ '^[[:graph:]]+$' AS is_graph, b ~ '^[[:print:]]+$' AS is_print, b ~ '^[[:punct:]]+$' AS is_punct, b ~ '^[[:space:]]+$' AS is_space FROM collate_test6; b | is_alpha | is_upper | is_lower | is_digit | is_alnum | is_graph | is_print | is_punct | is_spa -----+----------+----------+----------+----------+----------+----------+-------- abc | t | f | t | f | t | t | t | f | f ABC | t | t | f | f | t | t | t | f | f 123 | f | f | f | t | t | t | t | f | f ab1 | f | f | f | f | t | t | t | f | f a1! | f | f | f | f | f | t | t | f | f a c | f | f | f | f | f | f | t | f | f !.; | f | f | f | f | f | t | t | t | f | f | f | f | f | f | f | t | f | t äbç | t | f | t | f | t | t | t | f | f ÄBÇ | t | t | f | f | t | t | t | f | f (10 rows) SELECT 'Türkiye' COLLATE "en-x-icu" ~* 'KI' AS "true"; true ------ t (1 row) SELECT 'Türkiye' COLLATE "tr-x-icu" ~* 'KI' AS "true"; -- true with ICU true ------ t (1 row) SELECT 'bıt' ~* 'BIT' COLLATE "en-x-icu" AS "false"; false ------- f (1 row) SELECT 'bıt' ~* 'BIT' COLLATE "tr-x-icu" AS "false"; -- false with ICU false ------- f (1 row) -- The following actually exercises the selectivity estimation for ~*. SELECT relname FROM pg_class WHERE relname ~* '^abc'; relname --------- (0 rows) /* not run by default because it requires tr_TR system locale -- to_char SET lc_time TO 'tr_TR'; SELECT to_char(date '2010-04-01', 'DD TMMON YYYY'); SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr-x-icu"); */ -- backwards parsing CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "C") FROM collate_test10; SELECT table_name, view_definition FROM information_schema.views WHERE table_name LIKE 'collview%' ORDER BY 1; table_name | view_definition ------------+-------------------------------------------- collview1 | SELECT a, + | b + | FROM collate_test1 + | WHERE ((b COLLATE "C") >= 'bbc'::text); collview2 | SELECT a, + | b + | FROM collate_test1 + | ORDER BY (b COLLATE "C"); collview3 | SELECT a, + | lower(((x || x) COLLATE "C")) AS lower+ | FROM collate_test10; (3 rows) -- collation propagation in various expression types SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; a | coalesce ---+---------- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; a | coalesce ---+---------- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, coalesce(b, 'foo') FROM collate_test3 ORDER BY 2; a | coalesce ---+---------- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10; a | lower | lower ---+-------+------- 1 | hij | hij 2 | hij | hıj (2 rows) SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; a | b | greatest ---+-----+---------- 1 | abc | CCC 2 | äbc | CCC 3 | bbc | CCC 4 | ABC | CCC (4 rows) SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; a | b | greatest ---+-----+---------- 1 | abc | CCC 3 | bbc | CCC 4 | ABC | CCC 2 | äbc | äbc (4 rows) SELECT a, b, greatest(b, 'CCC') FROM collate_test3 ORDER BY 3; a | b | greatest ---+-----+---------- 4 | ABC | CCC 1 | abc | abc 3 | bbc | bbc 2 | äbc | äbc (4 rows) SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10; a | x | y | lower | lower ---+-----+-----+-------+------- 1 | hij | hij | hij | hij 2 | HIJ | HIJ | hij | hıj (2 rows) SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; a | nullif ---+-------- 4 | ABC 2 | äbc 3 | bbc 1 | (4 rows) SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; a | nullif ---+-------- 4 | ABC 3 | bbc 2 | äbc 1 | (4 rows) SELECT a, nullif(b, 'abc') FROM collate_test3 ORDER BY 2; a | nullif ---+-------- 4 | ABC 3 | bbc 2 | äbc 1 | (4 rows) SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10; a | lower | lower ---+-------+------- 1 | hij | hij 2 | hij | hıj (2 rows) SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; a | b ---+------ 4 | ABC 2 | äbc 1 | abcd 3 | bbc (4 rows) SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; a | b ---+------ 4 | ABC 1 | abcd 3 | bbc 2 | äbc (4 rows) SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test3 ORDER BY 2; a | b ---+------ 4 | ABC 1 | abcd 3 | bbc 2 | äbc (4 rows) CREATE DOMAIN testdomain AS text; SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, b::testdomain FROM collate_test3 ORDER BY 2; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) SELECT a, b::testdomain_sv FROM collate_test3 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10; a | lower | lower ---+-------+------- 1 | hij | hij 2 | hij | hıj (2 rows) SELECT min(b), max(b) FROM collate_test1; min | max -----+----- abc | bbc (1 row) SELECT min(b), max(b) FROM collate_test2; min | max -----+----- abc | äbc (1 row) SELECT min(b), max(b) FROM collate_test3; min | max -----+----- ABC | äbc (1 row) SELECT array_agg(b ORDER BY b) FROM collate_test1; array_agg ------------------- {abc,ABC,äbc,bbc} (1 row) SELECT array_agg(b ORDER BY b) FROM collate_test2; array_agg ------------------- {abc,ABC,bbc,äbc} (1 row) SELECT array_agg(b ORDER BY b) FROM collate_test3; array_agg ------------------- {ABC,abc,bbc,äbc} (1 row) SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; a | b ---+----- 1 | abc 1 | abc 4 | ABC 4 | ABC 2 | äbc 2 | äbc 3 | bbc 3 | bbc (8 rows) SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, b FROM collate_test3 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test3 WHERE a > 1 ORDER a | b ---+----- 3 | bbc 2 | äbc (2 rows) SELECT a, b FROM collate_test3 EXCEPT SELECT a, b FROM collate_test3 WHERE a < 2 ORDER BY 2; a | b ---+----- 4 | ABC 3 | bbc 2 | äbc (3 rows) SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3 ORDER BY 2; -- fail ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly. SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- ok a | b ---+----- 1 | abc 2 | äbc 3 | bbc 4 | ABC 1 | abc 2 | äbc 3 | bbc 4 | ABC (8 rows) SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- fail ERROR: collation mismatch between implicit collations "en-x-icu" and "C" LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat... ^ HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- ok a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail ERROR: collation mismatch between implicit collations "en-x-icu" and "C" LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col... ^ HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail ERROR: collation mismatch between implicit collations "en-x-icu" and "C" LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla... ^ HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; ERROR: no collation was derived for column "b" with collatable type text HINT: Use the COLLATE clause to set the collation explicitly. -- ideally this would be a parse-time error, but for now it must be run-time: select x < y from collate_test10; -- fail ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly. select x || y from collate_test10; -- ok, because || is not collation aware ?column? ---------- hijhij HIJHIJ (2 rows) select x, y from collate_test10 order by x || y; -- not so ok ERROR: collation mismatch between implicit collations "en-x-icu" and "tr-x-icu" LINE 1: select x, y from collate_test10 order by x || y; ^ HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. -- collation mismatch between recursive and non-recursive term WITH RECURSIVE foo(x) AS (SELECT x FROM (VALUES('a' COLLATE "en-x-icu"),('b')) t(x) UNION ALL SELECT (x || 'c') COLLATE "de-x-icu" FROM foo WHERE length(x) < 10) SELECT * FROM foo; ERROR: recursive query "foo" column 1 has collation "en-x-icu" in non-recursive term but coll LINE 2: (SELECT x FROM (VALUES('a' COLLATE "en-x-icu"),('b')) t(x... ^ HINT: Use the COLLATE clause to set the collation of the non-recursive term. -- casting SELECT CAST('42' AS text COLLATE "C"); ERROR: syntax error at or near "COLLATE" LINE 1: SELECT CAST('42' AS text COLLATE "C"); ^ SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; a | b ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, CAST(b AS varchar) FROM collate_test3 ORDER BY 2; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) -- propagation of collation in SQL functions (inlined and non-inlined cases) -- and plpgsql functions too CREATE FUNCTION mylt (text, text) RETURNS boolean LANGUAGE sql AS $$ select $1 < $2 $$; CREATE FUNCTION mylt_noninline (text, text) RETURNS boolean LANGUAGE sql AS $$ select $1 < $2 limit 1 $$; CREATE FUNCTION mylt_plpgsql (text, text) RETURNS boolean LANGUAGE plpgsql AS $$ begin return $1 < $2; end $$; SELECT a.b AS a, b.b AS b, a.b < b.b AS lt, mylt(a.b, b.b), mylt_noninline(a.b, b.b), mylt_plpgsql(a.b, b.b) FROM collate_test1 a, collate_test1 b ORDER BY a.b, b.b; a | b | lt | mylt | mylt_noninline | mylt_plpgsql -----+-----+----+------+----------------+-------------- abc | abc | f | f | f | f abc | ABC | t | t | t | t abc | äbc | t | t | t | t abc | bbc | t | t | t | t ABC | abc | f | f | f | f ABC | ABC | f | f | f | f ABC | äbc | t | t | t | t ABC | bbc | t | t | t | t äbc | abc | f | f | f | f äbc | ABC | f | f | f | f äbc | äbc | f | f | f | f äbc | bbc | t | t | t | t bbc | abc | f | f | f | f bbc | ABC | f | f | f | f bbc | äbc | f | f | f | f bbc | bbc | f | f | f | f (16 rows) SELECT a.b AS a, b.b AS b, a.b < b.b COLLATE "C" AS lt, mylt(a.b, b.b COLLATE "C"), mylt_noninline(a.b, b.b COLLATE "C"), mylt_plpgsql(a.b, b.b COLLATE "C") FROM collate_test1 a, collate_test1 b ORDER BY a.b, b.b; a | b | lt | mylt | mylt_noninline | mylt_plpgsql -----+-----+----+------+----------------+-------------- abc | abc | f | f | f | f abc | ABC | f | f | f | f abc | äbc | t | t | t | t abc | bbc | t | t | t | t ABC | abc | t | t | t | t ABC | ABC | f | f | f | f ABC | äbc | t | t | t | t ABC | bbc | t | t | t | t äbc | abc | f | f | f | f äbc | ABC | f | f | f | f äbc | äbc | f | f | f | f äbc | bbc | f | f | f | f bbc | abc | f | f | f | f bbc | ABC | f | f | f | f bbc | äbc | t | t | t | t bbc | bbc | f | f | f | f (16 rows) -- collation override in plpgsql CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ declare xx text := x; yy text := y; begin return xx < yy; end $$; SELECT mylt2('a', 'B' collate "en-x-icu") as t, mylt2('a', 'B' collate "C") as f; t | f ---+--- t | f (1 row) CREATE OR REPLACE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ declare xx text COLLATE "POSIX" := x; yy text := y; begin return xx < yy; end $$; SELECT mylt2('a', 'B') as f; f --- f (1 row) SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly. CONTEXT: PL/pgSQL function mylt2(text,text) line 6 at RETURN SELECT mylt2('a', 'B' collate "POSIX") as f; f --- f (1 row) -- polymorphism SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; unnest -------- abc ABC äbc bbc (4 rows) SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; unnest -------- abc ABC bbc äbc (4 rows) SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test3)) ORDER BY 1; unnest -------- ABC abc bbc äbc (4 rows) CREATE FUNCTION dup (anyelement) RETURNS anyelement AS 'select $1' LANGUAGE sql; SELECT a, dup(b) FROM collate_test1 ORDER BY 2; a | dup ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) SELECT a, dup(b) FROM collate_test2 ORDER BY 2; a | dup ---+----- 1 | abc 4 | ABC 3 | bbc 2 | äbc (4 rows) SELECT a, dup(b) FROM collate_test3 ORDER BY 2; a | dup ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) -- indexes CREATE INDEX collate_test1_idx1 ON collate_test1 (b); CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "C"); CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "C")); -- this is different gra CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "C"); -- fail ERROR: collations are not supported by type integer CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail ERROR: collations are not supported by type integer LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C... ^ SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx relname | pg_get_indexdef --------------------+----------------------------------------------------------- collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_tests.collate_test1 USI collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_tests.collate_test1 USI collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_tests.collate_test1 USI collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USI (4 rows) set enable_seqscan = off; explain (costs off) select * from collate_test1 where b ilike 'abc'; QUERY PLAN ------------------------------- Seq Scan on collate_test1 Disabled: true Filter: (b ~~* 'abc'::text) (3 rows) select * from collate_test1 where b ilike 'abc'; a | b ---+----- 1 | abc 4 | ABC (2 rows) explain (costs off) select * from collate_test1 where b ilike 'ABC'; QUERY PLAN ------------------------------- Seq Scan on collate_test1 Disabled: true Filter: (b ~~* 'ABC'::text) (3 rows) select * from collate_test1 where b ilike 'ABC'; a | b ---+----- 1 | abc 4 | ABC (2 rows) reset enable_seqscan; -- schema manipulation commands CREATE ROLE regress_test_role; CREATE SCHEMA test_schema; -- We need to do this this way to cope with varying names for encodings: SET client_min_messages TO WARNING; SET icu_validation_level = disabled; do $$ BEGIN EXECUTE 'CREATE COLLATION test0 (provider = icu, locale = ' || quote_literal((SELECT CASE WHEN datlocprovider='i' THEN datlocale ELSE datcollate END FRO END $$; CREATE COLLATION test0 FROM "C"; -- fail, duplicate name ERROR: collation "test0" already exists do $$ BEGIN EXECUTE 'CREATE COLLATION test1 (provider = icu, locale = ' || quote_literal((SELECT CASE WHEN datlocprovider='i' THEN datlocale ELSE datcollate END FRO END $$; RESET icu_validation_level; RESET client_min_messages; CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, needs "locale" ERROR: parameter "locale" must be specified SET icu_validation_level = ERROR; CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); -- fails ERROR: ICU locale "nonsense-nowhere" has unknown language "nonsense" HINT: To disable ICU locale validation, set the parameter "icu_validation_level" to "disabl CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); -- ERROR: could not convert locale name "@colStrength=primary;nonsense=yes" to language tag: RESET icu_validation_level; CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DR WARNING: could not convert locale name "@colStrength=primary;nonsense=yes" to language ta CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx; WARNING: ICU locale "nonsense-nowhere" has unknown language "nonsense" HINT: To disable ICU locale validation, set the parameter "icu_validation_level" to "disabl CREATE COLLATION test4 FROM nonsense; ERROR: collation "nonsense" for encoding "UTF8" does not exist CREATE COLLATION test5 FROM test0; SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1; collname ---------- test0 test1 test5 (3 rows) ALTER COLLATION test1 RENAME TO test11; ALTER COLLATION test0 RENAME TO test11; -- fail ERROR: collation "test11" already exists in schema "collate_tests" ALTER COLLATION test1 RENAME TO test22; -- fail ERROR: collation "test1" for encoding "UTF8" does not exist ALTER COLLATION test11 OWNER TO regress_test_role; ALTER COLLATION test11 OWNER TO nonsense; ERROR: role "nonsense" does not exist ALTER COLLATION test11 SET SCHEMA test_schema; COMMENT ON COLLATION test0 IS 'US English'; SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation') FROM pg_collation JOIN pg_namespace ON (collnamespace = pg_namespace.oid) WHERE collname LIKE 'test%' ORDER BY 1; collname | nspname | obj_description ----------+---------------+----------------- test0 | collate_tests | US English test11 | test_schema | test5 | collate_tests | (3 rows) DROP COLLATION test0, test_schema.test11, test5; DROP COLLATION test0; -- fail ERROR: collation "test0" for encoding "UTF8" does not exist DROP COLLATION IF EXISTS test0; NOTICE: collation "test0" does not exist, skipping SELECT collname FROM pg_collation WHERE collname LIKE 'test%'; collname ---------- (0 rows) DROP SCHEMA test_schema; DROP ROLE regress_test_role; -- ALTER ALTER COLLATION "en-x-icu" REFRESH VERSION; NOTICE: version has not changed -- also test for database while we are here SELECT current_database() AS datname \gset ALTER DATABASE :"datname" REFRESH COLLATION VERSION; NOTICE: version has not changed -- dependencies CREATE COLLATION test0 FROM "C"; CREATE TABLE collate_dep_test1 (a int, b text COLLATE test0); CREATE DOMAIN collate_dep_dom1 AS text COLLATE test0; CREATE TYPE collate_dep_test2 AS (x int, y text COLLATE test0); CREATE VIEW collate_dep_test3 AS SELECT text 'foo' COLLATE test0 AS foo; CREATE TABLE collate_dep_test4t (a int, b text); CREATE INDEX collate_dep_test4i ON collate_dep_test4t (b COLLATE test0); DROP COLLATION test0 RESTRICT; -- fail ERROR: cannot drop collation test0 because other objects depend on it DETAIL: column b of table collate_dep_test1 depends on collation test0 type collate_dep_dom1 depends on collation test0 column y of composite type collate_dep_test2 depends on collation test0 view collate_dep_test3 depends on collation test0 index collate_dep_test4i depends on collation test0 HINT: Use DROP ... CASCADE to drop the dependent objects too. DROP COLLATION test0 CASCADE; NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to column b of table collate_dep_test1 drop cascades to type collate_dep_dom1 drop cascades to column y of composite type collate_dep_test2 drop cascades to view collate_dep_test3 drop cascades to index collate_dep_test4i \d collate_dep_test1 Table "collate_tests.collate_dep_test1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- a | integer | | | \d collate_dep_test2 Composite type "collate_tests.collate_dep_test2" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- x | integer | | | DROP TABLE collate_dep_test1, collate_dep_test4t; DROP TYPE collate_dep_test2; -- test range types and collations create type textrange_c as range(subtype=text, collation="C"); create type textrange_en_us as range(subtype=text, collation="en-x-icu"); select textrange_c('A','Z') @> 'b'::text; ?column? ---------- f (1 row) select textrange_en_us('A','Z') @> 'b'::text; ?column? ---------- t (1 row) drop type textrange_c; drop type textrange_en_us; -- standard collations SELECT * FROM collate_test2 ORDER BY b COLLATE UCS_BASIC; a | b ---+----- 4 | ABC 1 | abc 3 | bbc 2 | äbc (4 rows) SELECT * FROM collate_test2 ORDER BY b COLLATE UNICODE; a | b ---+----- 1 | abc 4 | ABC 2 | äbc 3 | bbc (4 rows) -- test ICU collation customization -- test the attributes handled by icu_set_collation_attributes() SET client_min_messages=WARNING; CREATE COLLATION testcoll_ignore_accents (provider = icu, locale = '@colStrength=primary RESET client_min_messages; SELECT 'aaá' > 'AAA' COLLATE "und-x-icu", 'aaá' < 'AAA' COLLATE testcoll_ignore_accents; ?column? | ?column? ----------+---------- t | t (1 row) SET client_min_messages=WARNING; CREATE COLLATION testcoll_backwards (provider = icu, locale = '@colBackwards=yes'); RESET client_min_messages; SELECT 'coté' < 'côte' COLLATE "und-x-icu", 'coté' > 'côte' COLLATE testcoll_backwards; ?column? | ?column? ----------+---------- t | t (1 row) CREATE COLLATION testcoll_lower_first (provider = icu, locale = '@colCaseFirst=lower'); NOTICE: using standard form "und-u-kf-lower" for ICU locale "@colCaseFirst=lower" CREATE COLLATION testcoll_upper_first (provider = icu, locale = '@colCaseFirst=upper'); NOTICE: using standard form "und-u-kf-upper" for ICU locale "@colCaseFirst=upper" SELECT 'aaa' < 'AAA' COLLATE testcoll_lower_first, 'aaa' > 'AAA' COLLATE testcoll_upper_firs ?column? | ?column? ----------+---------- t | t (1 row) CREATE COLLATION testcoll_shifted (provider = icu, locale = '@colAlternate=shifted'); NOTICE: using standard form "und-u-ka-shifted" for ICU locale "@colAlternate=shifted" SELECT 'de-luge' < 'deanza' COLLATE "und-x-icu", 'de-luge' > 'deanza' COLLATE testcoll_shift ?column? | ?column? ----------+---------- t | t (1 row) SET client_min_messages=WARNING; CREATE COLLATION testcoll_numeric (provider = icu, locale = '@colNumeric=yes'); RESET client_min_messages; SELECT 'A-21' > 'A-123' COLLATE "und-x-icu", 'A-21' < 'A-123' COLLATE testcoll_numeric; ?column? | ?column? ----------+---------- t | t (1 row) CREATE COLLATION testcoll_error1 (provider = icu, locale = '@colNumeric=lower'); NOTICE: using standard form "und-u-kn-lower" for ICU locale "@colNumeric=lower" ERROR: could not open collator for locale "und-u-kn-lower": U_ILLEGAL_ARGUMENT_ERROR -- test that attributes not handled by icu_set_collation_attributes() -- (handled by ucol_open() directly) also work CREATE COLLATION testcoll_de_phonebook (provider = icu, locale = 'de@collation=phonebook NOTICE: using standard form "de-u-co-phonebk" for ICU locale "de@collation=phonebook" SELECT 'Goldmann' < 'Götz' COLLATE "de-x-icu", 'Goldmann' > 'Götz' COLLATE testcoll_de_phonebo ?column? | ?column? ----------+---------- t | t (1 row) -- rules CREATE COLLATION testcoll_rules1 (provider = icu, locale = '', rules = '&a < g'); NOTICE: using standard form "und" for ICU locale "" CREATE TABLE test7 (a text); -- example from https://unicode-org.github.io/icu/userguide/collation/customization INSERT INTO test7 VALUES ('Abernathy'), ('apple'), ('bird'), ('Boston'), ('Graham'), ('gr SELECT * FROM test7 ORDER BY a COLLATE "en-x-icu"; a ----------- Abernathy apple bird Boston Graham green (6 rows) SELECT * FROM test7 ORDER BY a COLLATE testcoll_rules1; a ----------- Abernathy apple green bird Boston Graham (6 rows) DROP TABLE test7; CREATE COLLATION testcoll_rulesx (provider = icu, locale = '', rules = '!!wrong!!'); NOTICE: using standard form "und" for ICU locale "" ERROR: could not open collator for locale "und" with rules "!!wrong!!": U_INVALID_FORMAT_ER -- nondeterministic collations CREATE COLLATION ctest_det (provider = icu, locale = '', deterministic = true); NOTICE: using standard form "und" for ICU locale "" CREATE COLLATION ctest_nondet (provider = icu, locale = '', deterministic = false); NOTICE: using standard form "und" for ICU locale "" SELECT 'abc' LIKE 'abc' COLLATE ctest_det; ?column? ---------- t (1 row) SELECT 'abc' LIKE 'a\bc' COLLATE ctest_det; ?column? ---------- t (1 row) SELECT 'abc' LIKE 'abc' COLLATE ctest_nondet; ?column? ---------- t (1 row) SELECT 'abc' LIKE 'a\bc' COLLATE ctest_nondet; ?column? ---------- t (1 row) CREATE TABLE test6 (a int, b text); -- same string in different normal forms INSERT INTO test6 VALUES (1, U&'zy\00E4bc'); INSERT INTO test6 VALUES (2, U&'zy\0061\0308bc'); SELECT * FROM test6; a | b ---+------- 1 | zyäbc 2 | zyäbc (2 rows) SELECT * FROM test6 WHERE b = 'zyäbc' COLLATE ctest_det; a | b ---+------- 1 | zyäbc (1 row) SELECT * FROM test6 WHERE b = 'zyäbc' COLLATE ctest_nondet; a | b ---+------- 1 | zyäbc 2 | zyäbc (2 rows) SELECT strpos(b COLLATE ctest_det, 'bc') FROM test6; strpos -------- 4 5 (2 rows) SELECT strpos(b COLLATE ctest_nondet, 'bc') FROM test6; strpos -------- 4 5 (2 rows) SELECT replace(b COLLATE ctest_det, U&'\00E4b', 'X') FROM test6; replace --------- zyXc zyäbc (2 rows) SELECT replace(b COLLATE ctest_nondet, U&'\00E4b', 'X') FROM tes replace --------- zyXc zyXc (2 rows) SELECT a, split_part(b COLLATE ctest_det, U&'\00E4b', 2) FROM tes a | split_part ---+------------ 1 | c 2 | (2 rows) SELECT a, split_part(b COLLATE ctest_nondet, U&'\00E4b', 2) FROM a | split_part ---+------------ 1 | c 2 | c (2 rows) SELECT a, split_part(b COLLATE ctest_det, U&'\00E4b', -1) FROM te a | split_part ---+------------ 1 | c 2 | zyäbc (2 rows) SELECT a, split_part(b COLLATE ctest_nondet, U&'\00E4b', -1) FRO a | split_part ---+------------ 1 | c 2 | c (2 rows) SELECT a, string_to_array(b COLLATE ctest_det, U&'\00E4b') FROM a | string_to_array ---+----------------- 1 | {zy,c} 2 | {zyäbc} (2 rows) SELECT a, string_to_array(b COLLATE ctest_nondet, U&'\00E4b') F a | string_to_array ---+----------------- 1 | {zy,c} 2 | {zy,c} (2 rows) SELECT * FROM test6 WHERE b LIKE 'zyäbc' COLLATE ctest_det; a | b ---+------- 1 | zyäbc (1 row) SELECT * FROM test6 WHERE b LIKE 'zyäbc' COLLATE ctest_nondet; a | b ---+------- 1 | zyäbc 2 | zyäbc (2 rows) -- same with arrays CREATE TABLE test6a (a int, b text[]); INSERT INTO test6a VALUES (1, ARRAY[U&'\00E4bc']); INSERT INTO test6a VALUES (2, ARRAY[U&'\0061\0308bc']); SELECT * FROM test6a; a | b ---+------- 1 | {äbc} 2 | {äbc} (2 rows) SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_det; a | b ---+------- 1 | {äbc} (1 row) SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet; a | b ---+------- 1 | {äbc} 2 | {äbc} (2 rows) CREATE COLLATION case_sensitive (provider = icu, locale = ''); NOTICE: using standard form "und" for ICU locale "" CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', det NOTICE: using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary" SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive; ?column? | ?column? ----------+---------- t | f (1 row) SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_insensitive; ?column? | ?column? ----------+---------- t | t (1 row) -- tests with array_sort SELECT array_sort('{a,B}'::text[] COLLATE case_insensitive); array_sort ------------ {a,B} (1 row) SELECT array_sort('{a,B}'::text[] COLLATE "C"); array_sort ------------ {B,a} (1 row) -- test replace() at the end of the string (bug #19341) SELECT replace('testX' COLLATE case_insensitive, 'x' COLLATE case_insensitive, 'er'); replace --------- tester (1 row) -- test language tags CREATE COLLATION lt_insensitive (provider = icu, locale = 'en-u-ks-level1', deterministic SELECT 'aBcD' COLLATE lt_insensitive = 'AbCd' COLLATE lt_insensitive; ?column? ---------- t (1 row) CREATE COLLATION lt_upperfirst (provider = icu, locale = 'und-u-kf-upper'); SELECT 'Z' COLLATE lt_upperfirst < 'z' COLLATE lt_upperfirst; ?column? ---------- t (1 row) CREATE TABLE test1cs (x text COLLATE case_sensitive); CREATE TABLE test2cs (x text COLLATE case_sensitive); CREATE TABLE test3cs (x text COLLATE case_sensitive); INSERT INTO test1cs VALUES ('abc'), ('def'), ('ghi'); INSERT INTO test2cs VALUES ('ABC'), ('ghi'); INSERT INTO test3cs VALUES ('abc'), ('ABC'), ('def'), ('ghi'); SELECT x FROM test3cs WHERE x = 'abc'; x ----- abc (1 row) SELECT x FROM test3cs WHERE x <> 'abc'; x ----- ABC def ghi (3 rows) SELECT x FROM test3cs WHERE x LIKE 'a%'; x ----- abc (1 row) SELECT x FROM test3cs WHERE x ILIKE 'a%'; x ----- abc ABC (2 rows) SELECT x FROM test3cs WHERE x SIMILAR TO 'a%'; x ----- abc (1 row) SELECT x FROM test3cs WHERE x ~ 'a'; x ----- abc (1 row) SET enable_hashagg TO off; SELECT x FROM test1cs UNION SELECT x FROM test2cs ORDER BY x; x ----- abc ABC def ghi (4 rows) SELECT x FROM test2cs UNION SELECT x FROM test1cs ORDER BY x; x ----- abc ABC def ghi (4 rows) SELECT x FROM test1cs INTERSECT SELECT x FROM test2cs; x ----- ghi (1 row) SELECT x FROM test2cs INTERSECT SELECT x FROM test1cs; x ----- ghi (1 row) SELECT x FROM test1cs EXCEPT SELECT x FROM test2cs; x ----- abc def (2 rows) SELECT x FROM test2cs EXCEPT SELECT x FROM test1cs; x ----- ABC (1 row) SELECT DISTINCT x FROM test3cs ORDER BY x; x ----- abc ABC def ghi (4 rows) RESET enable_hashagg; SELECT count(DISTINCT x) FROM test3cs; count ------- 4 (1 row) SELECT x, count(*) FROM test3cs GROUP BY x ORDER BY x; x | count -----+------- abc | 1 ABC | 1 def | 1 ghi | 1 (4 rows) SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3cs ORDER BY x; x | row_number | rank -----+------------+------ abc | 1 | 1 ABC | 2 | 2 def | 3 | 3 ghi | 4 | 4 (4 rows) CREATE UNIQUE INDEX ON test1cs (x); -- ok INSERT INTO test1cs VALUES ('ABC'); -- ok CREATE UNIQUE INDEX ON test3cs (x); -- ok SELECT string_to_array('ABC,DEF,GHI' COLLATE case_sensitive, ',', 'abc'); string_to_array ----------------- {ABC,DEF,GHI} (1 row) SELECT string_to_array('ABCDEFGHI' COLLATE case_sensitive, NULL, 'b'); string_to_array --------------------- {A,B,C,D,E,F,G,H,I} (1 row) -- -- A unique index under one collation does not prove uniqueness under -- another, so the planner must not use such a proof for any optimization. -- -- Ensure that we do not use inner-unique join execution EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM test1cs t1, test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; QUERY PLAN ---------------------------------------------------------------------- Sort Output: t1.x, t2.x Sort Key: t1.x COLLATE case_sensitive, t2.x COLLATE case_sensitive -> Hash Join Output: t1.x, t2.x Hash Cond: ((t2.x)::text = (t1.x)::text) -> Seq Scan on collate_tests.test3cs t2 Output: t2.x -> Hash Output: t1.x -> Seq Scan on collate_tests.test1cs t1 Output: t1.x (12 rows) SELECT * FROM test1cs t1, test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; x | x -----+----- abc | abc abc | ABC ABC | abc ABC | ABC def | def ghi | ghi (6 rows) -- Ensure that left-join is not removed EXPLAIN (COSTS OFF) SELECT t1.* FROM test3cs t1 LEFT JOIN test3cs t2 ON t1.x = t2.x COLLATE case_insensitive ORDER BY 1; QUERY PLAN ------------------------------------------ Sort Sort Key: t1.x COLLATE case_sensitive -> Hash Left Join Hash Cond: (t1.x = (t2.x)::text) -> Seq Scan on test3cs t1 -> Hash -> Seq Scan on test3cs t2 (7 rows) SELECT t1.* FROM test3cs t1 LEFT JOIN test3cs t2 ON t1.x = t2.x COLLATE case_insensitive ORDER BY 1; x ----- abc abc ABC ABC def ghi (6 rows) -- Ensure that self-join is not removed EXPLAIN (COSTS OFF) SELECT * FROM test3cs t1, test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; QUERY PLAN ---------------------------------------------------------------------- Sort Sort Key: t1.x COLLATE case_sensitive, t2.x COLLATE case_sensitive -> Hash Join Hash Cond: ((t1.x)::text = (t2.x)::text) -> Seq Scan on test3cs t1 -> Hash -> Seq Scan on test3cs t2 (7 rows) SELECT * FROM test3cs t1, test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; x | x -----+----- abc | abc abc | ABC ABC | abc ABC | ABC def | def ghi | ghi (6 rows) -- Ensure that semijoin is not reduced to innerjoin EXPLAIN (COSTS OFF) SELECT * FROM test3cs t1 WHERE EXISTS (SELECT 1 FROM test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; QUERY PLAN -------------------------------------------------- Sort Sort Key: t1.x COLLATE case_sensitive -> Hash Semi Join Hash Cond: ((t1.x)::text = (t2.x)::text) -> Seq Scan on test3cs t1 -> Hash -> Seq Scan on test3cs t2 (7 rows) SELECT * FROM test3cs t1 WHERE EXISTS (SELECT 1 FROM test3cs t2 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; x ----- abc ABC def ghi (4 rows) -- -- A DISTINCT / GROUP BY / set-op on a subquery does not prove uniqueness -- under a different collation, so the planner must not use such a proof for -- any optimization. -- -- Ensure that we do not use inner-unique join execution EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; QUERY PLAN --------------------------------------------------------------------------- Sort Output: t1.x, test3cs.x Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive -> Hash Join Output: t1.x, test3cs.x Hash Cond: ((t1.x)::text = (test3cs.x)::text) -> Seq Scan on collate_tests.test1cs t1 Output: t1.x -> Hash Output: test3cs.x -> HashAggregate Output: test3cs.x Group Key: test3cs.x -> Seq Scan on collate_tests.test3cs Output: test3cs.x (15 rows) SELECT * FROM test1cs t1, (SELECT DISTINCT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; x | x -----+----- abc | abc abc | ABC ABC | abc ABC | ABC def | def ghi | ghi (6 rows) -- Same with GROUP BY EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; QUERY PLAN --------------------------------------------------------------------------- Sort Output: t1.x, test3cs.x Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive -> Hash Join Output: t1.x, test3cs.x Hash Cond: ((t1.x)::text = (test3cs.x)::text) -> Seq Scan on collate_tests.test1cs t1 Output: t1.x -> Hash Output: test3cs.x -> HashAggregate Output: test3cs.x Group Key: test3cs.x -> Seq Scan on collate_tests.test3cs Output: test3cs.x (15 rows) SELECT * FROM test1cs t1, (SELECT x FROM test3cs GROUP BY x) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; x | x -----+----- abc | abc abc | ABC ABC | abc ABC | ABC def | def ghi | ghi (6 rows) -- Same with set-op EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; QUERY PLAN --------------------------------------------------------------------------- Sort Output: t1.x, test3cs.x Sort Key: t1.x COLLATE case_sensitive, test3cs.x COLLATE case_sensitive -> Hash Join Output: t1.x, test3cs.x Hash Cond: ((test3cs.x)::text = (t1.x)::text) -> HashAggregate Output: test3cs.x Group Key: test3cs.x -> Append -> Seq Scan on collate_tests.test3cs Output: test3cs.x -> Seq Scan on collate_tests.test3cs test3cs_1 Output: test3cs_1.x -> Hash Output: t1.x -> Seq Scan on collate_tests.test1cs t1 Output: t1.x (18 rows) SELECT * FROM test1cs t1, (SELECT x FROM test3cs UNION SELECT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive ORDER BY 1, 2; x | x -----+----- abc | abc abc | ABC ABC | abc ABC | ABC def | def ghi | ghi (6 rows) -- Ensure that left-join is not removed EXPLAIN (COSTS OFF) SELECT t1.* FROM test3cs t1 LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive ORDER BY 1; QUERY PLAN ----------------------------------------------- Sort Sort Key: t1.x COLLATE case_sensitive -> Hash Left Join Hash Cond: (t1.x = (test3cs.x)::text) -> Seq Scan on test3cs t1 -> Hash -> HashAggregate Group Key: test3cs.x -> Seq Scan on test3cs (9 rows) SELECT t1.* FROM test3cs t1 LEFT JOIN (SELECT DISTINCT x FROM test3cs) t2 ON t1.x = t2.x COLLATE case_insensitive ORDER BY 1; x ----- abc abc ABC ABC def ghi (6 rows) -- Ensure that semijoin is not reduced to innerjoin EXPLAIN (COSTS OFF) SELECT * FROM test3cs t1 WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; QUERY PLAN ------------------------------------------------------- Sort Sort Key: t1.x COLLATE case_sensitive -> Hash Semi Join Hash Cond: ((t1.x)::text = (test3cs.x)::text) -> Seq Scan on test3cs t1 -> Hash -> HashAggregate Group Key: test3cs.x -> Seq Scan on test3cs (9 rows) SELECT * FROM test3cs t1 WHERE EXISTS (SELECT 1 FROM (SELECT DISTINCT x FROM test3cs) t2 WHERE t1.x = t2.x COLLATE case_insensitive) ORDER BY 1; x ----- abc ABC def ghi (4 rows) CREATE TABLE test1ci (x text COLLATE case_insensitive); CREATE TABLE test2ci (x text COLLATE case_insensitive); CREATE TABLE test3ci (x text COLLATE case_insensitive); CREATE INDEX ON test3ci (x text_pattern_ops); -- error ERROR: nondeterministic collations are not supported for operator class "text_pattern_ops INSERT INTO test1ci VALUES ('abc'), ('def'), ('ghi'); INSERT INTO test2ci VALUES ('ABC'), ('ghi'); INSERT INTO test3ci VALUES ('abc'), ('ABC'), ('def'), ('ghi'); SELECT x FROM test3ci WHERE x = 'abc'; x ----- abc ABC (2 rows) SELECT x FROM test3ci WHERE x <> 'abc'; x ----- def ghi (2 rows) SELECT x FROM test3ci WHERE x LIKE 'a%'; x ----- abc ABC (2 rows) SELECT x FROM test3ci WHERE x ILIKE 'a%'; ERROR: nondeterministic collations are not supported for ILIKE SELECT x FROM test3ci WHERE x SIMILAR TO 'a%'; ERROR: nondeterministic collations are not supported for regular expressions SELECT x FROM test3ci WHERE x ~ 'a'; ERROR: nondeterministic collations are not supported for regular expressions SELECT x FROM test1ci UNION SELECT x FROM test2ci ORDER BY x; x ----- abc def ghi (3 rows) SELECT x FROM test2ci UNION SELECT x FROM test1ci ORDER BY x; x ----- ABC def ghi (3 rows) SELECT x FROM test1ci INTERSECT SELECT x FROM test2ci ORDER BY x; x ----- abc ghi (2 rows) SELECT x FROM test2ci INTERSECT SELECT x FROM test1ci ORDER BY x; x ----- ABC ghi (2 rows) SELECT x FROM test1ci EXCEPT SELECT x FROM test2ci; x ----- def (1 row) SELECT x FROM test2ci EXCEPT SELECT x FROM test1ci; x --- (0 rows) SELECT DISTINCT x FROM test3ci ORDER BY x; x ----- abc def ghi (3 rows) SELECT count(DISTINCT x) FROM test3ci; count ------- 3 (1 row) SELECT x, count(*) FROM test3ci GROUP BY x ORDER BY x; x | count -----+------- abc | 2 def | 1 ghi | 1 (3 rows) SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3ci ORDER BY x; x | row_number | rank -----+------------+------ abc | 1 | 1 ABC | 2 | 1 def | 3 | 3 ghi | 4 | 4 (4 rows) CREATE UNIQUE INDEX ON test1ci (x); -- ok INSERT INTO test1ci VALUES ('ABC'); -- error ERROR: duplicate key value violates unique constraint "test1ci_x_idx" DETAIL: Key (x)=(ABC) already exists. CREATE UNIQUE INDEX ON test3ci (x); -- error ERROR: could not create unique index "test3ci_x_idx" DETAIL: Key (x)=(abc) is duplicated. SELECT string_to_array('ABC,DEF,GHI' COLLATE case_insensitive, ',', 'abc'); string_to_array ----------------- {NULL,DEF,GHI} (1 row) SELECT string_to_array('ABCDEFGHI' COLLATE case_insensitive, NULL, 'b'); string_to_array ------------------------ {A,NULL,C,D,E,F,G,H,I} (1 row) -- Test HAVING-to-WHERE pushdown with nondeterministic collations. -- When a HAVING clause uses a different collation than the GROUP BY's -- nondeterministic collation, it must not be pushed to WHERE, otherwise -- aggregate results can change because the stricter filter eliminates rows -- before grouping. -- Negative: collation conflict, HAVING must not be pushed to WHERE EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive; QUERY PLAN ---------------------------------------------------- HashAggregate Group Key: x Filter: (x = 'abc'::text COLLATE case_sensitive) -> Seq Scan on test3ci (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive; x | count -----+------- abc | 2 (1 row) -- Positive: same collation, safe to push HAVING to WHERE EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_insensitive; QUERY PLAN ------------------------------------------------------------ GroupAggregate -> Seq Scan on test3ci Filter: (x = 'abc'::text COLLATE case_insensitive) (3 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_insensitive; x | count -----+------- abc | 2 (1 row) -- Negative: function applied to grouped column with conflicting collation EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x) = 'ABC' COLLATE case_sensitive; QUERY PLAN ----------------------------------------------------------- HashAggregate Group Key: x Filter: (upper(x) = 'ABC'::text COLLATE case_sensitive) -> Seq Scan on test3ci (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x) = 'ABC' COLLATE case_sensitive; x | count -----+------- abc | 2 (1 row) -- Positive: function with same collation as GROUP BY EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x) = 'ABC' COLLATE case_insensitive; QUERY PLAN ------------------------------------------------------------------------- GroupAggregate Group Key: x -> Sort Sort Key: x COLLATE case_insensitive -> Seq Scan on test3ci Filter: (upper(x) = 'ABC'::text COLLATE case_insensitive) (6 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x) = 'ABC' COLLATE case_insensitive; x | count -----+------- abc | 2 (1 row) -- Negative: inner function has conflicting collation, even though outer -- operator's collation matches GROUP BY due to a COLLATE override EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x COLLATE case_sensitive) COLLATE cas QUERY PLAN ---------------------------------------------------- HashAggregate Group Key: x Filter: ((upper((x)::text))::text = 'ABC'::text) -> Seq Scan on test3ci (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING upper(x COLLATE case_sensitive) COLLATE cas x | count -----+------- abc | 2 (1 row) -- Mixed AND: conflicting clause stays in HAVING, safe clause pushed to WHERE EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive AND length(x QUERY PLAN ---------------------------------------------------- HashAggregate Group Key: x Filter: (x = 'abc'::text COLLATE case_sensitive) -> Seq Scan on test3ci Filter: (length(x) > 1) (5 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive AND length(x x | count -----+------- abc | 2 (1 row) -- Positive: AND of two safe clauses, both can be pushed EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_insensitive AND length QUERY PLAN -------------------------------------------------------------------------------- GroupAggregate -> Seq Scan on test3ci Filter: ((x = 'abc'::text COLLATE case_insensitive) AND (length(x) > 1)) (3 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_insensitive AND length x | count -----+------- abc | 2 (1 row) -- Negative: OR with a conflicting clause: must stay in HAVING EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive OR x = 'def' CO QUERY PLAN -------------------------------------------------------------------------------- Sort Sort Key: x COLLATE case_insensitive -> HashAggregate Group Key: x Filter: ((x = 'abc'::text COLLATE case_sensitive) OR (x = 'def'::text COLLATE case_sensitiv -> Seq Scan on test3ci (6 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING x = 'abc' COLLATE case_sensitive OR x = 'def' CO x | count -----+------- abc | 2 def | 1 (2 rows) -- Negative: collation conflict inside a RowCompareExpr EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING ROW(x, 1) < ROW('ABC' COLLATE case_sensitive, QUERY PLAN -------------------------------------------------------------------------- Sort Sort Key: x COLLATE case_insensitive -> HashAggregate Group Key: x Filter: (ROW(x, 1) < ROW('ABC'::text COLLATE case_sensitive, 1)) -> Seq Scan on test3ci (6 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING ROW(x, 1) < ROW('ABC' COLLATE case_sensitive, x | count -----+------- abc | 2 (1 row) -- Negative: simple-CASE form with conflicting WHEN comparison collation EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE x WHEN 'abc' COLLATE case_sensitive THE QUERY PLAN -------------------------------------------------------------------------------- HashAggregate Group Key: x Filter: CASE x WHEN 'abc'::text COLLATE case_sensitive THEN true ELSE false END -> Seq Scan on test3ci (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE x WHEN 'abc' COLLATE case_sensitive THE x | count -----+------- abc | 2 (1 row) -- Positive: simple-CASE form with matching collation, safe to push EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE x WHEN 'abc' COLLATE case_insensitive T QUERY PLAN -------------------------------------------------------------------------------- HashAggregate Group Key: x -> Seq Scan on test3ci Filter: CASE x WHEN 'abc'::text COLLATE case_insensitive THEN true ELSE false END (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE x WHEN 'abc' COLLATE case_insensitive T x | count -----+------- abc | 2 (1 row) -- Negative: nested CASE with collation conflict EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE WHEN (CASE x WHEN 'abc' COLLATE case_sen QUERY PLAN -------------------------------------------------------------------------------- HashAggregate Group Key: x Filter: CASE WHEN (CASE x WHEN 'abc'::text COLLATE case_sensitive THEN 1 ELSE 0 END = 1) THEN tru -> Seq Scan on test3ci (4 rows) SELECT x, count(*) FROM test3ci GROUP BY x HAVING (CASE WHEN (CASE x WHEN 'abc' COLLATE case_sen x | count -----+------- abc | 2 (1 row) -- Positive: conflicting collation but no grouping expression reference EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3ci GROUP BY x HAVING current_setting('server_version') = 'abc' QUERY PLAN -------------------------------------------------------------------------------- HashAggregate Group Key: x -> Result One-Time Filter: (current_setting('server_version'::text) = 'abc'::text COLLATE case_s -> Seq Scan on test3ci (5 rows) -- Positive: deterministic collation in GROUP BY: always safe to push, even if -- HAVING uses a nondeterministic collation EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3cs GROUP BY x HAVING x = 'abc' COLLATE case_sensitive; QUERY PLAN ---------------------------------------------------------- GroupAggregate -> Seq Scan on test3cs Filter: (x = 'abc'::text COLLATE case_sensitive) (3 rows) SELECT x, count(*) FROM test3cs GROUP BY x HAVING x = 'abc' COLLATE case_sensitive; x | count -----+------- abc | 1 (1 row) EXPLAIN (COSTS OFF) SELECT x, count(*) FROM test3cs GROUP BY x HAVING x = 'abc' COLLATE case_insensitive ORDER BY 1; QUERY PLAN ------------------------------------------------------------------ GroupAggregate Group Key: x -> Sort Sort Key: x COLLATE case_sensitive -> Seq Scan on test3cs Filter: (x = 'abc'::text COLLATE case_insensitive) (6 rows) SELECT x, count(*) FROM test3cs GROUP BY x HAVING x = 'abc' COLLATE case_insensitive ORDER BY 1; x | count -----+------- abc | 1 ABC | 1 (2 rows) -- bpchar CREATE TABLE test1bpci (x char(3) COLLATE case_insensitive); CREATE TABLE test2bpci (x char(3) COLLATE case_insensitive); CREATE TABLE test3bpci (x char(3) COLLATE case_insensitive); CREATE INDEX ON test3bpci (x bpchar_pattern_ops); -- error ERROR: nondeterministic collations are not supported for operator class "bpchar_pattern_o INSERT INTO test1bpci VALUES ('abc'), ('def'), ('ghi'); INSERT INTO test2bpci VALUES ('ABC'), ('ghi'); INSERT INTO test3bpci VALUES ('abc'), ('ABC'), ('def'), ('ghi'); SELECT x FROM test3bpci WHERE x = 'abc'; x ----- abc ABC (2 rows) SELECT x FROM test3bpci WHERE x <> 'abc'; x ----- def ghi (2 rows) SELECT x FROM test3bpci WHERE x LIKE 'a%'; x ----- abc ABC (2 rows) SELECT x FROM test3bpci WHERE x ILIKE 'a%'; ERROR: nondeterministic collations are not supported for ILIKE SELECT x FROM test3bpci WHERE x SIMILAR TO 'a%'; ERROR: nondeterministic collations are not supported for regular expressions SELECT x FROM test3bpci WHERE x ~ 'a'; ERROR: nondeterministic collations are not supported for regular expressions SELECT x FROM test1bpci UNION SELECT x FROM test2bpci ORDER BY x; x ----- abc def ghi (3 rows) SELECT x FROM test2bpci UNION SELECT x FROM test1bpci ORDER BY x; x ----- ABC def ghi (3 rows) SELECT x FROM test1bpci INTERSECT SELECT x FROM test2bpci ORDER BY x; x ----- abc ghi (2 rows) SELECT x FROM test2bpci INTERSECT SELECT x FROM test1bpci ORDER BY x; x ----- ABC ghi (2 rows) SELECT x FROM test1bpci EXCEPT SELECT x FROM test2bpci; x ----- def (1 row) SELECT x FROM test2bpci EXCEPT SELECT x FROM test1bpci; x --- (0 rows) SELECT DISTINCT x FROM test3bpci ORDER BY x; x ----- abc def ghi (3 rows) SELECT count(DISTINCT x) FROM test3bpci; count ------- 3 (1 row) SELECT x, count(*) FROM test3bpci GROUP BY x ORDER BY x; x | count -----+------- abc | 2 def | 1 ghi | 1 (3 rows) SELECT x, row_number() OVER (ORDER BY x), rank() OVER (ORDER BY x) FROM test3bpci ORDER BY x; x | row_number | rank -----+------------+------ abc | 1 | 1 ABC | 2 | 1 def | 3 | 3 ghi | 4 | 4 (4 rows) CREATE UNIQUE INDEX ON test1bpci (x); -- ok INSERT INTO test1bpci VALUES ('ABC'); -- error ERROR: duplicate key value violates unique constraint "test1bpci_x_idx" DETAIL: Key (x)=(ABC) already exists. CREATE UNIQUE INDEX ON test3bpci (x); -- error ERROR: could not create unique index "test3bpci_x_idx" DETAIL: Key (x)=(abc) is duplicated. SELECT string_to_array('ABC,DEF,GHI'::char(11) COLLATE case_insensitive, ',', 'abc'); string_to_array ----------------- {NULL,DEF,GHI} (1 row) SELECT string_to_array('ABCDEFGHI'::char(9) COLLATE case_insensitive, NULL, 'b'); string_to_array ------------------------ {A,NULL,C,D,E,F,G,H,I} (1 row) -- This tests the issue described in match_pattern_prefix(). In the -- absence of that check, the case_insensitive tests below would -- return no rows where they should logically return one. CREATE TABLE test4c (x text COLLATE case_insensitive); INSERT INTO test4c VALUES ('abc'); CREATE INDEX ON test4c (x); SET enable_seqscan = off; SELECT x FROM test4c WHERE x LIKE 'ABC' COLLATE case_sensitive; -- ok, no rows x --- (0 rows) SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_sensitive; -- ok, no rows x --- (0 rows) SELECT x FROM test4c WHERE x LIKE 'ABC' COLLATE case_insensitive; -- ok x ----- abc (1 row) SELECT x FROM test4c WHERE x LIKE 'ABC%' COLLATE case_insensitive; -- ok x ----- abc (1 row) RESET enable_seqscan; -- Unicode special case: different variants of Greek lower case sigma. -- A naive implementation like citext that just does lower(x) = -- lower(y) will do the wrong thing here, because lower('Σ') is 'σ' -- but upper('ς') is 'Σ'. SELECT 'ὀδυσσεύς' = 'ὈΔΥΣΣΕΎΣ' COLLATE case_sensitive; ?column? ---------- f (1 row) SELECT 'ὀδυσσεύς' = 'ὈΔΥΣΣΕΎΣ' COLLATE case_insensitive; ?column? ---------- t (1 row) -- name vs. text comparison operators SELECT relname FROM pg_class WHERE relname = 'PG_CLASS'::text COLLATE case_insensitive; relname ---------- pg_class (1 row) SELECT relname FROM pg_class WHERE 'PG_CLASS'::text = relname COLLATE case_insensitive; relname ---------- pg_class (1 row) SELECT typname FROM pg_type WHERE typname LIKE 'int_' AND typname <> 'INT2'::text COLLATE case_insensitive ORDER BY typname; typname --------- int4 int8 (2 rows) SELECT typname FROM pg_type WHERE typname LIKE 'int_' AND 'INT2'::text <> typname COLLATE case_insensitive ORDER BY typname; typname --------- int4 int8 (2 rows) -- test case adapted from subselect.sql CREATE TEMP TABLE outer_text (f1 text COLLATE case_insensitive, f2 text); INSERT INTO outer_text VALUES ('a', 'a'); INSERT INTO outer_text VALUES ('b', 'a'); INSERT INTO outer_text VALUES ('A', NULL); INSERT INTO outer_text VALUES ('B', NULL); CREATE TEMP TABLE inner_text (c1 text COLLATE case_insensitive, c2 text); INSERT INTO inner_text VALUES ('a', NULL); SELECT * FROM outer_text WHERE (f1, f2) NOT IN (SELECT * FROM inner_text); f1 | f2 ----+---- b | a B | (2 rows) -- accents SET client_min_messages=WARNING; CREATE COLLATION ignore_accents (provider = icu, locale = '@colStrength=primary;colCaseL RESET client_min_messages; CREATE TABLE test4 (a int, b text); INSERT INTO test4 VALUES (1, 'cote'), (2, 'côte'), (3, 'coté'), (4, 'côté'); SELECT * FROM test4 WHERE b = 'cote'; a | b ---+------ 1 | cote (1 row) SELECT * FROM test4 WHERE b = 'cote' COLLATE ignore_accents; a | b ---+------ 1 | cote 2 | côte 3 | coté 4 | côté (4 rows) SELECT * FROM test4 WHERE b = 'Cote' COLLATE ignore_accents; -- still case-sensitive a | b ---+--- (0 rows) SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive; a | b ---+------ 1 | cote (1 row) CREATE TABLE test4nfd (a int, b text); INSERT INTO test4nfd VALUES (1, 'cote'), (2, 'côte'), (3, 'coté'), (4, 'côté'); UPDATE test4nfd SET b = normalize(b, nfd); -- This shows why replace should be greedy. Otherwise, in the NFD -- case, the match would stop before the decomposed accents, which -- would leave the accents in the results. SELECT a, b, replace(b COLLATE ignore_accents, 'co', 'ma') FROM test4; a | b | replace ---+------+--------- 1 | cote | mate 2 | côte | mate 3 | coté | maté 4 | côté | maté (4 rows) SELECT a, b, replace(b COLLATE ignore_accents, 'co', 'ma') FROM test4nfd; a | b | replace ---+------+--------- 1 | cote | mate 2 | côte | mate 3 | coté | maté 4 | côté | maté (4 rows) -- This is a tricky one. A naive implementation would first test -- \00E4 matches \0061, which is true under ignore_accents, but then -- the rest of the string won't match anymore. Therefore, the -- algorithm has to test whether the rest of the string matches, and -- if not try matching \00E4 against a longer substring like -- \0061\0308, which will then work out. SELECT U&'\0061\0308bc' LIKE U&'\00E4 ?column? ---------- t (1 row) -- and in reverse: SELECT U&'\00E4bc' LIKE U&'\0061\0308 ?column? ---------- t (1 row) -- inner % matches b: SELECT U&'\0061\0308bc' LIKE U&'\00E4 ?column? ---------- t (1 row) -- inner %% matches b then zero: SELECT U&'\0061\0308bc' LIKE U&'\00E4 ?column? ---------- t (1 row) -- inner %% matches b then zero: SELECT U&'cb\0061\0308' LIKE U&'c%%\00E4' C ?column? ---------- t (1 row) -- trailing _ matches two codepoints that form one grapheme: SELECT U&'cb\0061\0308' LIKE U&'cb_' COLLATE ignore_accents; ?column? ---------- f (1 row) -- trailing __ matches two codepoints that form one grapheme: SELECT U&'cb\0061\0308' LIKE U&'cb__' COLLATE ignore_accents; ?column? ---------- t (1 row) -- leading % matches zero: SELECT U&'\0061\0308bc' LIKE U&'%\00E4 ?column? ---------- t (1 row) -- leading % matches zero (with later %): SELECT U&'\0061\0308bc' LIKE U&'%\00E4 ?column? ---------- t (1 row) -- trailing % matches zero: SELECT U&'\0061\0308bc' LIKE U&'\00E4 ?column? ---------- t (1 row) -- trailing % matches zero (with previous %): SELECT U&'\0061\0308bc' LIKE U&'\00E4 ?column? ---------- t (1 row) -- _ versus two codepoints that form one grapheme: SELECT U&'\0061\0308bc' LIKE U&'_bc' COLLATE ignore_accents; ?column? ---------- t (1 row) -- (actually this matches because) SELECT U&'\0308bc' = 'bc' COLLATE ignore_accents; ?column? ---------- t (1 row) -- __ matches two codepoints that form one grapheme: SELECT U&'\0061\0308bc' LIKE U&'__bc' COLLATE ignore_accents; ?column? ---------- t (1 row) -- _ matches one codepoint that forms half a grapheme: SELECT U&'\0061\0308bc' LIKE U&'_\0308 ?column? ---------- t (1 row) -- doesn't match because \00e4 doesn't match only \0308 SELECT U&'\0061\0308bc' LIKE U&'_\00e4 ?column? ---------- f (1 row) -- escape character at end of pattern SELECT 'foox' LIKE 'foo\' COLLATE ignore_accents; ERROR: LIKE pattern must not end with escape character -- foreign keys (mixing different nondeterministic collations not allowed) CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY); CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CAS ERROR: foreign key constraint "test10fk_x_fkey" cannot be implemented DETAIL: Key columns "x" of the referencing table and "x" of the referenced table have incompati CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY); CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCA ERROR: foreign key constraint "test11fk_x_fkey" cannot be implemented DETAIL: Key columns "x" of the referencing table and "x" of the referenced table have incompati -- foreign key actions -- Some of the behaviors are most easily visible with a -- case-insensitive collation. CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDA INSERT INTO test12pk VALUES ('abc'); INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok SELECT * FROM test12pk; x ----- ABC (1 row) SELECT * FROM test12fk; -- no updates here a | b ---+----- 1 | abc 2 | ABC (2 rows) DROP TABLE test12pk, test12fk; CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDA INSERT INTO test12pk VALUES ('abc'); INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation ERROR: update or delete on table "test12pk" violates RESTRICT setting of foreign key constrai DETAIL: Key (x)=(abc) is referenced from table "test12fk". SELECT * FROM test12pk; x ----- abc (1 row) SELECT * FROM test12fk; a | b ---+----- 1 | abc 2 | ABC (2 rows) DROP TABLE test12pk, test12fk; CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDA INSERT INTO test12pk VALUES ('abc'); INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok SELECT * FROM test12pk; x ----- ABC (1 row) SELECT * FROM test12fk; -- was updated a | b ---+----- 1 | ABC 2 | ABC (2 rows) DROP TABLE test12pk, test12fk; -- partitioning CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST (b); CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc'); INSERT INTO test20 VALUES (1, 'abc'); INSERT INTO test20 VALUES (2, 'ABC'); SELECT * FROM test20_1; a | b ---+----- 1 | abc 2 | ABC (2 rows) CREATE TABLE test21 (a int, b text COLLATE case_insensitive) PARTITION BY RANGE (b); CREATE TABLE test21_1 PARTITION OF test21 FOR VALUES FROM ('ABC') TO ('DEF'); INSERT INTO test21 VALUES (1, 'abc'); INSERT INTO test21 VALUES (2, 'ABC'); SELECT * FROM test21_1; a | b ---+----- 1 | abc 2 | ABC (2 rows) CREATE TABLE test22 (a int, b text COLLATE case_sensitive) PARTITION BY HASH (b); CREATE TABLE test22_0 PARTITION OF test22 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test22_1 PARTITION OF test22 FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test22 VALUES (1, 'def'); INSERT INTO test22 VALUES (2, 'DEF'); -- they end up in different partitions SELECT (SELECT count(*) FROM test22_0) = (SELECT count(*) FROM test22_1); ?column? ---------- t (1 row) -- same with arrays CREATE TABLE test22a (a int, b text[] COLLATE case_sensitive) PARTITION BY HASH (b); CREATE TABLE test22a_0 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test22a_1 PARTITION OF test22a FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test22a VALUES (1, ARRAY['def']); INSERT INTO test22a VALUES (2, ARRAY['DEF']); -- they end up in different partitions SELECT (SELECT count(*) FROM test22a_0) = (SELECT count(*) FROM test22a_1); ?column? ---------- t (1 row) CREATE TABLE test23 (a int, b text COLLATE case_insensitive) PARTITION BY HASH (b); CREATE TABLE test23_0 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test23_1 PARTITION OF test23 FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test23 VALUES (1, 'def'); INSERT INTO test23 VALUES (2, 'DEF'); -- they end up in the same partition (but it's platform-dependent which one) SELECT (SELECT count(*) FROM test23_0) <> (SELECT count(*) FROM test23_1); ?column? ---------- t (1 row) -- same with arrays CREATE TABLE test23a (a int, b text[] COLLATE case_insensitive) PARTITION BY HASH (b); CREATE TABLE test23a_0 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test23a_1 PARTITION OF test23a FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test23a VALUES (1, ARRAY['def']); INSERT INTO test23a VALUES (2, ARRAY['DEF']); -- they end up in the same partition (but it's platform-dependent which one) SELECT (SELECT count(*) FROM test23a_0) <> (SELECT count(*) FROM test23a_1); ?column? ---------- t (1 row) CREATE TABLE test30 (a int, b char(3) COLLATE case_insensitive) PARTITION BY LIST (b); CREATE TABLE test30_1 PARTITION OF test30 FOR VALUES IN ('abc'); INSERT INTO test30 VALUES (1, 'abc'); INSERT INTO test30 VALUES (2, 'ABC'); SELECT * FROM test30_1; a | b ---+----- 1 | abc 2 | ABC (2 rows) CREATE TABLE test31 (a int, b char(3) COLLATE case_insensitive) PARTITION BY RANGE (b); CREATE TABLE test31_1 PARTITION OF test31 FOR VALUES FROM ('ABC') TO ('DEF'); INSERT INTO test31 VALUES (1, 'abc'); INSERT INTO test31 VALUES (2, 'ABC'); SELECT * FROM test31_1; a | b ---+----- 1 | abc 2 | ABC (2 rows) CREATE TABLE test32 (a int, b char(3) COLLATE case_sensitive) PARTITION BY HASH (b); CREATE TABLE test32_0 PARTITION OF test32 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test32_1 PARTITION OF test32 FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test32 VALUES (1, 'def'); INSERT INTO test32 VALUES (2, 'DEF'); -- they end up in different partitions SELECT (SELECT count(*) FROM test32_0) = (SELECT count(*) FROM test32_1); ?column? ---------- t (1 row) CREATE TABLE test33 (a int, b char(3) COLLATE case_insensitive) PARTITION BY HASH (b); CREATE TABLE test33_0 PARTITION OF test33 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE test33_1 PARTITION OF test33 FOR VALUES WITH (MODULUS 2, REMAINDER 1); INSERT INTO test33 VALUES (1, 'def'); INSERT INTO test33 VALUES (2, 'DEF'); -- they end up in the same partition (but it's platform-dependent which one) SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1); ?column? ---------- t (1 row) -- -- Bug #18568 -- -- Partitionwise aggregate (full or partial) should not be used when a -- partition key's collation doesn't match that of the GROUP BY column it is -- matched with. SET max_parallel_workers_per_gather TO 0; SET enable_incremental_sort TO off; CREATE TABLE pagg_tab3 (a text, c text collate case_insensitive) PARTITION BY LIST(c collate CREATE TABLE pagg_tab3_p1 PARTITION OF pagg_tab3 FOR VALUES IN ('a', 'b'); CREATE TABLE pagg_tab3_p2 PARTITION OF pagg_tab3 FOR VALUES IN ('B', 'A'); INSERT INTO pagg_tab3 SELECT i % 4 + 1, substr('abAB', (i % 4) + 1 , 1) FROM generate_series(0, 19) ANALYZE pagg_tab3; SET enable_partitionwise_aggregate TO false; EXPLAIN (COSTS OFF) SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_i QUERY PLAN ----------------------------------------------------------- Sort Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive -> HashAggregate Group Key: pagg_tab3.c -> Append -> Seq Scan on pagg_tab3_p2 pagg_tab3_1 -> Seq Scan on pagg_tab3_p1 pagg_tab3_2 (7 rows) SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_i upper | count -------+------- A | 10 B | 10 (2 rows) -- No "full" partitionwise aggregation allowed, though "partial" is allowed. SET enable_partitionwise_aggregate TO true; EXPLAIN (COSTS OFF) SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_i QUERY PLAN -------------------------------------------------------------- Sort Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive -> Finalize HashAggregate Group Key: pagg_tab3.c -> Append -> Partial HashAggregate Group Key: pagg_tab3.c -> Seq Scan on pagg_tab3_p2 pagg_tab3 -> Partial HashAggregate Group Key: pagg_tab3_1.c -> Seq Scan on pagg_tab3_p1 pagg_tab3_1 (11 rows) SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_i upper | count -------+------- A | 10 B | 10 (2 rows) -- OK to use full partitionwise aggregate after changing the GROUP BY column's -- collation to be the same as that of the partition key. EXPLAIN (COSTS OFF) SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; QUERY PLAN -------------------------------------------------------- Sort Sort Key: ((pagg_tab3.c)::text) COLLATE "C" -> Append -> HashAggregate Group Key: (pagg_tab3.c)::text -> Seq Scan on pagg_tab3_p2 pagg_tab3 -> HashAggregate Group Key: (pagg_tab3_1.c)::text -> Seq Scan on pagg_tab3_p1 pagg_tab3_1 (9 rows) SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; c | count ---+------- A | 5 B | 5 a | 5 b | 5 (4 rows) -- Partitionwise join should not be allowed too when the collation used by the -- join keys doesn't match the partition key collation. SET enable_partitionwise_join TO false; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1 QUERY PLAN ------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) -> Append -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash -> Append -> Seq Scan on pagg_tab3_p2 t2_1 -> Seq Scan on pagg_tab3_p1 t2_2 (13 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1 c | count ---+------- A | 100 B | 100 (2 rows) SET enable_partitionwise_join TO true; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1 QUERY PLAN ------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) -> Append -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash -> Append -> Seq Scan on pagg_tab3_p2 t2_1 -> Seq Scan on pagg_tab3_p1 t2_2 (13 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1 c | count ---+------- A | 100 B | 100 (2 rows) -- OK when the join clause uses the same collation as the partition key. EXPLAIN (COSTS OFF) SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE QUERY PLAN ------------------------------------------------------------------ Sort Sort Key: ((t1.c)::text) COLLATE "C" -> Append -> HashAggregate Group Key: (t1.c)::text -> Hash Join Hash Cond: ((t1.c)::text = (t2.c)::text) -> Seq Scan on pagg_tab3_p2 t1 -> Hash -> Seq Scan on pagg_tab3_p2 t2 -> HashAggregate Group Key: (t1_1.c)::text -> Hash Join Hash Cond: ((t1_1.c)::text = (t2_1.c)::text) -> Seq Scan on pagg_tab3_p1 t1_1 -> Hash -> Seq Scan on pagg_tab3_p1 t2_1 (17 rows) SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE c | count ---+------- A | 25 B | 25 a | 25 b | 25 (4 rows) SET enable_partitionwise_join TO false; EXPLAIN (COSTS OFF) SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE QUERY PLAN ------------------------------------------------------------- Sort Sort Key: ((t1.c)::text) COLLATE "C" -> HashAggregate Group Key: (t1.c)::text -> Hash Join Hash Cond: ((t1.c)::text = (t2.c)::text) -> Append -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash -> Append -> Seq Scan on pagg_tab3_p2 t2_1 -> Seq Scan on pagg_tab3_p1 t2_2 (13 rows) SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE c | count ---+------- A | 25 B | 25 a | 25 b | 25 (4 rows) -- Few other cases where the joined partition keys are matched via equivalence -- class, not a join restriction clause. -- Collations of joined columns match, but the partition keys collation is different SET enable_partitionwise_join TO true; CREATE TABLE pagg_tab4 (c text collate case_insensitive, b text collate case_insensitive) P CREATE TABLE pagg_tab4_p1 PARTITION OF pagg_tab4 FOR VALUES IN ('a', 'b'); CREATE TABLE pagg_tab4_p2 PARTITION OF pagg_tab4 FOR VALUES IN ('B', 'A'); INSERT INTO pagg_tab4 (b, c) SELECT substr('abAB', (i % 4) + 1 , 1), substr('abAB', (i % 2) + 1 , 1) F ANALYZE pagg_tab4; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP QUERY PLAN ------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) -> Append -> Seq Scan on pagg_tab3_p2 t1_1 -> Seq Scan on pagg_tab3_p1 t1_2 -> Hash -> Append -> Seq Scan on pagg_tab4_p2 t2_1 Filter: (c = b) -> Seq Scan on pagg_tab4_p1 t2_2 Filter: (c = b) (15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP c | count ---+------- A | 60 B | 60 (2 rows) -- OK when the partition key collation is same as that of the join columns CREATE TABLE pagg_tab5 (c text collate case_insensitive, b text collate case_insensitive) P CREATE TABLE pagg_tab5_p1 PARTITION OF pagg_tab5 FOR VALUES IN ('a', 'b'); CREATE TABLE pagg_tab5_p2 PARTITION OF pagg_tab5 FOR VALUES IN ('c', 'd'); INSERT INTO pagg_tab5 (b, c) SELECT substr('abAB', (i % 4) + 1 , 1), substr('abAB', (i % 2) + 1 , 1) F INSERT INTO pagg_tab5 (b, c) SELECT substr('cdCD', (i % 4) + 1 , 1), substr('cdCD', (i % 2) + 1 , 1) F ANALYZE pagg_tab5; CREATE TABLE pagg_tab6 (c text collate case_insensitive, b text collate case_insensitive) P CREATE TABLE pagg_tab6_p1 PARTITION OF pagg_tab6 FOR VALUES IN ('a', 'b'); CREATE TABLE pagg_tab6_p2 PARTITION OF pagg_tab6 FOR VALUES IN ('c', 'd'); INSERT INTO pagg_tab6 (b, c) SELECT substr('abAB', (i % 4) + 1 , 1), substr('abAB', (i % 2) + 1 , 1) F INSERT INTO pagg_tab6 (b, c) SELECT substr('cdCD', (i % 4) + 1 , 1), substr('cdCD', (i % 2) + 1 , 1) F ANALYZE pagg_tab6; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP QUERY PLAN ------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" -> Append -> HashAggregate Group Key: t1.c -> Nested Loop Join Filter: (t1.c = t2.c) -> Seq Scan on pagg_tab6_p1 t2 Filter: (c = b) -> Seq Scan on pagg_tab5_p1 t1 -> HashAggregate Group Key: t1_1.c -> Nested Loop Join Filter: (t1_1.c = t2_1.c) -> Seq Scan on pagg_tab6_p2 t2_1 Filter: (c = b) -> Seq Scan on pagg_tab5_p2 t1_1 (17 rows) SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP c | count ---+------- a | 9 b | 9 c | 9 d | 9 (4 rows) SET enable_partitionwise_join TO false; EXPLAIN (COSTS OFF) SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP QUERY PLAN ------------------------------------------------------------- Sort Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join Hash Cond: (t1.c = t2.c) -> Append -> Seq Scan on pagg_tab5_p1 t1_1 -> Seq Scan on pagg_tab5_p2 t1_2 -> Hash -> Append -> Seq Scan on pagg_tab6_p1 t2_1 Filter: (c = b) -> Seq Scan on pagg_tab6_p2 t2_2 Filter: (c = b) (15 rows) SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP c | count ---+------- a | 9 b | 9 c | 9 d | 9 (4 rows) DROP TABLE pagg_tab3; DROP TABLE pagg_tab4; DROP TABLE pagg_tab5; DROP TABLE pagg_tab6; RESET enable_partitionwise_aggregate; RESET max_parallel_workers_per_gather; RESET enable_incremental_sort; -- -- A unique index can prove functional dependency for GROUP BY column -- removal only if its per-column collation agrees on equality with -- the GROUP BY column's collation. An index built under a different -- (deterministic) collation would otherwise let remove_useless_groupby_columns -- drop other columns whose values still differ within a nondeterministic -- group. -- CREATE TABLE groupby_collation_t (a text COLLATE case_insensitive NOT NULL, b text); INSERT INTO groupby_collation_t VALUES ('foo', 'X'), ('FOO', 'Y'); CREATE UNIQUE INDEX ON groupby_collation_t (a COLLATE "C"); -- Column b must NOT be dropped: under case_insensitive on a, 'foo' and -- 'FOO' would merge, but they have distinct b values. EXPLAIN (COSTS OFF) SELECT a, b FROM groupby_collation_t GROUP BY a, b ORDER BY a, b; QUERY PLAN ------------------------------------------------- Group Group Key: a, b -> Sort Sort Key: a COLLATE case_insensitive, b -> Seq Scan on groupby_collation_t (5 rows) SELECT a, b FROM groupby_collation_t GROUP BY a, b ORDER BY a, b; a | b -----+--- foo | X FOO | Y (2 rows) DROP TABLE groupby_collation_t; -- virtual generated columns CREATE TABLE t5 ( a int, b text collate "C", c text collate "C" GENERATED ALWAYS AS (b COLLATE case_insensitive) ); INSERT INTO t5 (a, b) values (1, 'D1'), (2, 'D2'), (3, 'd1'); -- Collation of c should be the one defined for the column ("C"), not -- the one of the generation expression. (Note that we cannot just -- test with, say, using COLLATION FOR, because the collation of -- function calls is already determined in the parser before -- rewriting.) SELECT * FROM t5 ORDER BY c ASC, a ASC; a | b | c ---+----+---- 1 | D1 | D1 2 | D2 | D2 3 | d1 | d1 (3 rows) -- Check that DEFAULT expressions in SQL/JSON functions use the same collation -- as the RETURNING type. Mismatched collations should raise an error. CREATE DOMAIN d1 AS text COLLATE case_insensitive; CREATE DOMAIN d2 AS text COLLATE "C"; SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT ('C' COLLATE "C") COLLATE case_i ?column? ---------- t (1 row) SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' ON EMPTY) = 'a'; -- true ?column? ---------- t (1 row) SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON EMPTY) = 'a'; -- error ERROR: collation of DEFAULT expression conflicts with RETURNING clause LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C'::d2 ON... ^ DETAIL: "C" versus "case_insensitive" SELECT JSON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLATE "C" ON EMPTY) = 'a'; -- ERROR: collation of DEFAULT expression conflicts with RETURNING clause LINE 1: ...ON_VALUE('{"a": "A"}', '$.a' RETURNING d1 DEFAULT 'C' COLLAT... ^ DETAIL: "C" versus "case_insensitive" SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' ON EMPTY) = 'a'; -- true ?column? ---------- t (1 row) SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE case_insensitive ON ?column? ---------- t (1 row) SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON EMPTY) = 'a'; -- error ERROR: collation of DEFAULT expression conflicts with RETURNING clause LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A'::d2 ON... ^ DETAIL: "C" versus "case_insensitive" SELECT JSON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLATE "C" ON EMPTY) = 'a'; -- ERROR: collation of DEFAULT expression conflicts with RETURNING clause LINE 1: ...ON_VALUE('{"a": "A"}', '$.c' RETURNING d1 DEFAULT 'A' COLLAT... ^ DETAIL: "C" versus "case_insensitive" DROP DOMAIN d1, d2; -- cleanup RESET search_path; SET client_min_messages TO warning; DROP SCHEMA collate_tests CASCADE; RESET client_min_messages; -- leave a collation for pg_upgrade test CREATE COLLATION coll_icu_upgrade FROM "und-x-icu"; [Dauer der Verarbeitung: 0.49 Sekunden, vorverarbeitet 2026-08-08] |
2026-08-25