2 * This test is for ICU collations.
4 SET client_encoding TO UTF8;
5 CREATE SCHEMA collate_tests;
6 SET search_path = collate_tests;
7 CREATE TABLE collate_test1 (
9 b text COLLATE "en-x-icu" NOT NULL
12 Table "collate_tests.collate_test1"
13 Column | Type | Collation | Nullable | Default
14 --------+---------+-----------+----------+---------
16 b | text | en-x-icu | not null |
18 CREATE TABLE collate_test_fail (
20 b text COLLATE "ja_JP.eucjp-x-icu"
22 ERROR: collation "ja_JP.eucjp-x-icu" for encoding "UTF8" does not exist
23 LINE 3: b text COLLATE "ja_JP.eucjp-x-icu"
25 CREATE TABLE collate_test_fail (
27 b text COLLATE "foo-x-icu"
29 ERROR: collation "foo-x-icu" for encoding "UTF8" does not exist
30 LINE 3: b text COLLATE "foo-x-icu"
32 CREATE TABLE collate_test_fail (
33 a int COLLATE "en-x-icu",
36 ERROR: collations are not supported by type integer
37 LINE 2: a int COLLATE "en-x-icu",
39 CREATE TABLE collate_test_like (
43 Table "collate_tests.collate_test_like"
44 Column | Type | Collation | Nullable | Default
45 --------+---------+-----------+----------+---------
47 b | text | en-x-icu | not null |
49 CREATE TABLE collate_test2 (
51 b text COLLATE "sv-x-icu"
53 CREATE TABLE collate_test3 (
57 INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'äbc'), (3, 'bbc'), (4, 'ABC');
58 INSERT INTO collate_test2 SELECT * FROM collate_test1;
59 INSERT INTO collate_test3 SELECT * FROM collate_test1;
60 SELECT * FROM collate_test1 WHERE b >= 'bbc';
66 SELECT * FROM collate_test2 WHERE b >= 'bbc';
73 SELECT * FROM collate_test3 WHERE b >= 'bbc';
80 SELECT * FROM collate_test3 WHERE b >= 'BBC';
88 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
95 SELECT * FROM collate_test1 WHERE b >= 'bbc' COLLATE "C";
102 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "C";
109 SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "en-x-icu";
110 ERROR: collation mismatch between explicit collations "C" and "en-x-icu"
111 LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "e...
113 CREATE DOMAIN testdomain_sv AS text COLLATE "sv-x-icu";
114 CREATE DOMAIN testdomain_i AS int COLLATE "sv-x-icu"; -- fails
115 ERROR: collations are not supported by type integer
116 CREATE TABLE collate_test4 (
120 INSERT INTO collate_test4 SELECT * FROM collate_test1;
121 SELECT a, b FROM collate_test4 ORDER BY b;
130 CREATE TABLE collate_test5 (
132 b testdomain_sv COLLATE "en-x-icu"
134 INSERT INTO collate_test5 SELECT * FROM collate_test1;
135 SELECT a, b FROM collate_test5 ORDER BY b;
144 SELECT a, b FROM collate_test1 ORDER BY b;
153 SELECT a, b FROM collate_test2 ORDER BY b;
162 SELECT a, b FROM collate_test3 ORDER BY b;
171 SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
181 SELECT * FROM collate_test1 ORDER BY b;
190 SELECT * FROM collate_test2 ORDER BY b;
199 SELECT * FROM collate_test3 ORDER BY b;
208 -- constant expression folding
209 SELECT 'bbc' COLLATE "en-x-icu" > 'äbc' COLLATE "en-x-icu" AS "true";
215 SELECT 'bbc' COLLATE "sv-x-icu" > 'äbc' COLLATE "sv-x-icu" AS "false";
222 CREATE TABLE collate_test10 (
224 x text COLLATE "en-x-icu",
225 y text COLLATE "tr-x-icu"
227 INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ');
228 SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10;
229 a | lower | lower | upper | upper | initcap | initcap
230 ---+-------+-------+-------+-------+---------+---------
231 1 | hij | hij | HIJ | HİJ | Hij | Hij
232 2 | hij | hıj | HIJ | HIJ | Hij | Hıj
235 SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10;
242 SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a;
250 SELECT * FROM collate_test1 WHERE b LIKE 'abc';
256 SELECT * FROM collate_test1 WHERE b LIKE 'abc%';
262 SELECT * FROM collate_test1 WHERE b LIKE '%bc%';
270 SELECT * FROM collate_test1 WHERE b ILIKE 'abc';
277 SELECT * FROM collate_test1 WHERE b ILIKE 'abc%';
284 SELECT * FROM collate_test1 WHERE b ILIKE '%bc%';
293 SELECT 'Türkiye' COLLATE "en-x-icu" ILIKE '%KI%' AS "true";
299 SELECT 'Türkiye' COLLATE "tr-x-icu" ILIKE '%KI%' AS "false";
305 SELECT 'bıt' ILIKE 'BIT' COLLATE "en-x-icu" AS "false";
311 SELECT 'bıt' ILIKE 'BIT' COLLATE "tr-x-icu" AS "true";
317 -- The following actually exercises the selectivity estimation for ILIKE.
318 SELECT relname FROM pg_class WHERE relname ILIKE 'abc%';
323 -- regular expressions
324 SELECT * FROM collate_test1 WHERE b ~ '^abc$';
330 SELECT * FROM collate_test1 WHERE b ~ '^abc';
336 SELECT * FROM collate_test1 WHERE b ~ 'bc';
344 SELECT * FROM collate_test1 WHERE b ~* '^abc$';
351 SELECT * FROM collate_test1 WHERE b ~* '^abc';
358 SELECT * FROM collate_test1 WHERE b ~* 'bc';
367 CREATE TABLE collate_test6 (
369 b text COLLATE "en-x-icu"
371 INSERT INTO collate_test6 VALUES (1, 'abc'), (2, 'ABC'), (3, '123'), (4, 'ab1'),
372 (5, 'a1!'), (6, 'a c'), (7, '!.;'), (8, ' '),
373 (9, 'äbç'), (10, 'ÄBÇ');
375 b ~ '^[[:alpha:]]+$' AS is_alpha,
376 b ~ '^[[:upper:]]+$' AS is_upper,
377 b ~ '^[[:lower:]]+$' AS is_lower,
378 b ~ '^[[:digit:]]+$' AS is_digit,
379 b ~ '^[[:alnum:]]+$' AS is_alnum,
380 b ~ '^[[:graph:]]+$' AS is_graph,
381 b ~ '^[[:print:]]+$' AS is_print,
382 b ~ '^[[:punct:]]+$' AS is_punct,
383 b ~ '^[[:space:]]+$' AS is_space
385 b | is_alpha | is_upper | is_lower | is_digit | is_alnum | is_graph | is_print | is_punct | is_space
386 -----+----------+----------+----------+----------+----------+----------+----------+----------+----------
387 abc | t | f | t | f | t | t | t | f | f
388 ABC | t | t | f | f | t | t | t | f | f
389 123 | f | f | f | t | t | t | t | f | f
390 ab1 | f | f | f | f | t | t | t | f | f
391 a1! | f | f | f | f | f | t | t | f | f
392 a c | f | f | f | f | f | f | t | f | f
393 !.; | f | f | f | f | f | t | t | t | f
394 | f | f | f | f | f | f | t | f | t
395 äbç | t | f | t | f | t | t | t | f | f
396 ÄBÇ | t | t | f | f | t | t | t | f | f
399 SELECT 'Türkiye' COLLATE "en-x-icu" ~* 'KI' AS "true";
405 SELECT 'Türkiye' COLLATE "tr-x-icu" ~* 'KI' AS "true"; -- true with ICU
411 SELECT 'bıt' ~* 'BIT' COLLATE "en-x-icu" AS "false";
417 SELECT 'bıt' ~* 'BIT' COLLATE "tr-x-icu" AS "false"; -- false with ICU
423 -- The following actually exercises the selectivity estimation for ~*.
424 SELECT relname FROM pg_class WHERE relname ~* '^abc';
429 /* not run by default because it requires tr_TR system locale
432 SET lc_time TO 'tr_TR';
433 SELECT to_char(date '2010-04-01', 'DD TMMON YYYY');
434 SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr-x-icu");
437 CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
438 CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
439 CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "C") FROM collate_test10;
440 SELECT table_name, view_definition FROM information_schema.views
441 WHERE table_name LIKE 'collview%' ORDER BY 1;
442 table_name | view_definition
443 ------------+--------------------------------------------------------------------------
444 collview1 | SELECT collate_test1.a, +
446 | FROM collate_test1 +
447 | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text);
448 collview2 | SELECT collate_test1.a, +
450 | FROM collate_test1 +
451 | ORDER BY (collate_test1.b COLLATE "C");
452 collview3 | SELECT collate_test10.a, +
453 | lower(((collate_test10.x || collate_test10.x) COLLATE "C")) AS lower+
454 | FROM collate_test10;
457 -- collation propagation in various expression types
458 SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2;
467 SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2;
476 SELECT a, coalesce(b, 'foo') FROM collate_test3 ORDER BY 2;
485 SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10;
492 SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3;
501 SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3;
510 SELECT a, b, greatest(b, 'CCC') FROM collate_test3 ORDER BY 3;
519 SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10;
520 a | x | y | lower | lower
521 ---+-----+-----+-------+-------
522 1 | hij | hij | hij | hij
523 2 | HIJ | HIJ | hij | hıj
526 SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2;
535 SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2;
544 SELECT a, nullif(b, 'abc') FROM collate_test3 ORDER BY 2;
553 SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10;
560 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2;
569 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2;
578 SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test3 ORDER BY 2;
587 CREATE DOMAIN testdomain AS text;
588 SELECT a, b::testdomain FROM collate_test1 ORDER BY 2;
597 SELECT a, b::testdomain FROM collate_test2 ORDER BY 2;
606 SELECT a, b::testdomain FROM collate_test3 ORDER BY 2;
615 SELECT a, b::testdomain_sv FROM collate_test3 ORDER BY 2;
624 SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10;
631 SELECT min(b), max(b) FROM collate_test1;
637 SELECT min(b), max(b) FROM collate_test2;
643 SELECT min(b), max(b) FROM collate_test3;
649 SELECT array_agg(b ORDER BY b) FROM collate_test1;
655 SELECT array_agg(b ORDER BY b) FROM collate_test2;
661 SELECT array_agg(b ORDER BY b) FROM collate_test3;
667 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2;
680 SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2;
689 SELECT a, b FROM collate_test3 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test3 WHERE a > 1 ORDER BY 2;
696 SELECT a, b FROM collate_test3 EXCEPT SELECT a, b FROM collate_test3 WHERE a < 2 ORDER BY 2;
704 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
705 ERROR: could not determine which collation to use for string comparison
706 HINT: Use the COLLATE clause to set the collation explicitly.
707 SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- ok
720 SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
721 ERROR: collation mismatch between implicit collations "en-x-icu" and "C"
722 LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat...
724 HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.
725 SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- ok
734 SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
735 ERROR: collation mismatch between implicit collations "en-x-icu" and "C"
736 LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col...
738 HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.
739 SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail
740 ERROR: collation mismatch between implicit collations "en-x-icu" and "C"
741 LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla...
743 HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.
744 CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail
745 ERROR: no collation was derived for column "b" with collatable type text
746 HINT: Use the COLLATE clause to set the collation explicitly.
747 -- ideally this would be a parse-time error, but for now it must be run-time:
748 select x < y from collate_test10; -- fail
749 ERROR: could not determine which collation to use for string comparison
750 HINT: Use the COLLATE clause to set the collation explicitly.
751 select x || y from collate_test10; -- ok, because || is not collation aware
758 select x, y from collate_test10 order by x || y; -- not so ok
759 ERROR: collation mismatch between implicit collations "en-x-icu" and "tr-x-icu"
760 LINE 1: select x, y from collate_test10 order by x || y;
762 HINT: You can choose the collation by applying the COLLATE clause to one or both expressions.
763 -- collation mismatch between recursive and non-recursive term
764 WITH RECURSIVE foo(x) AS
765 (SELECT x FROM (VALUES('a' COLLATE "en-x-icu"),('b')) t(x)
767 SELECT (x || 'c') COLLATE "de-x-icu" FROM foo WHERE length(x) < 10)
769 ERROR: recursive query "foo" column 1 has collation "en-x-icu" in non-recursive term but collation "de-x-icu" overall
770 LINE 2: (SELECT x FROM (VALUES('a' COLLATE "en-x-icu"),('b')) t(x...
772 HINT: Use the COLLATE clause to set the collation of the non-recursive term.
774 SELECT CAST('42' AS text COLLATE "C");
775 ERROR: syntax error at or near "COLLATE"
776 LINE 1: SELECT CAST('42' AS text COLLATE "C");
778 SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2;
787 SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2;
796 SELECT a, CAST(b AS varchar) FROM collate_test3 ORDER BY 2;
805 -- propagation of collation in SQL functions (inlined and non-inlined cases)
806 -- and plpgsql functions too
807 CREATE FUNCTION mylt (text, text) RETURNS boolean LANGUAGE sql
808 AS $$ select $1 < $2 $$;
809 CREATE FUNCTION mylt_noninline (text, text) RETURNS boolean LANGUAGE sql
810 AS $$ select $1 < $2 limit 1 $$;
811 CREATE FUNCTION mylt_plpgsql (text, text) RETURNS boolean LANGUAGE plpgsql
812 AS $$ begin return $1 < $2; end $$;
813 SELECT a.b AS a, b.b AS b, a.b < b.b AS lt,
814 mylt(a.b, b.b), mylt_noninline(a.b, b.b), mylt_plpgsql(a.b, b.b)
815 FROM collate_test1 a, collate_test1 b
817 a | b | lt | mylt | mylt_noninline | mylt_plpgsql
818 -----+-----+----+------+----------------+--------------
819 abc | abc | f | f | f | f
820 abc | ABC | t | t | t | t
821 abc | äbc | t | t | t | t
822 abc | bbc | t | t | t | t
823 ABC | abc | f | f | f | f
824 ABC | ABC | f | f | f | f
825 ABC | äbc | t | t | t | t
826 ABC | bbc | t | t | t | t
827 äbc | abc | f | f | f | f
828 äbc | ABC | f | f | f | f
829 äbc | äbc | f | f | f | f
830 äbc | bbc | t | t | t | t
831 bbc | abc | f | f | f | f
832 bbc | ABC | f | f | f | f
833 bbc | äbc | f | f | f | f
834 bbc | bbc | f | f | f | f
837 SELECT a.b AS a, b.b AS b, a.b < b.b COLLATE "C" AS lt,
838 mylt(a.b, b.b COLLATE "C"), mylt_noninline(a.b, b.b COLLATE "C"),
839 mylt_plpgsql(a.b, b.b COLLATE "C")
840 FROM collate_test1 a, collate_test1 b
842 a | b | lt | mylt | mylt_noninline | mylt_plpgsql
843 -----+-----+----+------+----------------+--------------
844 abc | abc | f | f | f | f
845 abc | ABC | f | f | f | f
846 abc | äbc | t | t | t | t
847 abc | bbc | t | t | t | t
848 ABC | abc | t | t | t | t
849 ABC | ABC | f | f | f | f
850 ABC | äbc | t | t | t | t
851 ABC | bbc | t | t | t | t
852 äbc | abc | f | f | f | f
853 äbc | ABC | f | f | f | f
854 äbc | äbc | f | f | f | f
855 äbc | bbc | f | f | f | f
856 bbc | abc | f | f | f | f
857 bbc | ABC | f | f | f | f
858 bbc | äbc | t | t | t | t
859 bbc | bbc | f | f | f | f
862 -- collation override in plpgsql
863 CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
871 SELECT mylt2('a', 'B' collate "en-x-icu") as t, mylt2('a', 'B' collate "C") as f;
877 CREATE OR REPLACE FUNCTION
878 mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$
880 xx text COLLATE "POSIX" := x;
886 SELECT mylt2('a', 'B') as f;
892 SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations
893 ERROR: could not determine which collation to use for string comparison
894 HINT: Use the COLLATE clause to set the collation explicitly.
895 CONTEXT: PL/pgSQL function mylt2(text,text) line 6 at RETURN
896 SELECT mylt2('a', 'B' collate "POSIX") as f;
903 SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1;
912 SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1;
921 SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test3)) ORDER BY 1;
930 CREATE FUNCTION dup (anyelement) RETURNS anyelement
931 AS 'select $1' LANGUAGE sql;
932 SELECT a, dup(b) FROM collate_test1 ORDER BY 2;
941 SELECT a, dup(b) FROM collate_test2 ORDER BY 2;
950 SELECT a, dup(b) FROM collate_test3 ORDER BY 2;
960 CREATE INDEX collate_test1_idx1 ON collate_test1 (b);
961 CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "C");
962 CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "C")); -- this is different grammatically
963 CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX"));
964 CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "C"); -- fail
965 ERROR: collations are not supported by type integer
966 CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail
967 ERROR: collations are not supported by type integer
968 LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C...
970 SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1;
971 relname | pg_get_indexdef
972 --------------------+-------------------------------------------------------------------------------------------------------------------
973 collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_tests.collate_test1 USING btree (b)
974 collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_tests.collate_test1 USING btree (b COLLATE "C")
975 collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_tests.collate_test1 USING btree (b COLLATE "C")
976 collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX")
979 -- schema manipulation commands
980 CREATE ROLE regress_test_role;
981 CREATE SCHEMA test_schema;
982 -- We need to do this this way to cope with varying names for encodings:
985 EXECUTE 'CREATE COLLATION test0 (provider = icu, locale = ' ||
986 quote_literal(current_setting('lc_collate')) || ');';
989 CREATE COLLATION test0 FROM "C"; -- fail, duplicate name
990 ERROR: collation "test0" already exists
993 EXECUTE 'CREATE COLLATION test1 (provider = icu, lc_collate = ' ||
994 quote_literal(current_setting('lc_collate')) ||
996 quote_literal(current_setting('lc_ctype')) || ');';
999 CREATE COLLATION test3 (provider = icu, lc_collate = 'en_US.utf8'); -- fail, need lc_ctype
1000 ERROR: parameter "lc_ctype" must be specified
1001 CREATE COLLATION testx (provider = icu, locale = 'nonsense'); /* never fails with ICU */ DROP COLLATION testx;
1002 CREATE COLLATION test4 FROM nonsense;
1003 ERROR: collation "nonsense" for encoding "UTF8" does not exist
1004 CREATE COLLATION test5 FROM test0;
1005 SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1;
1013 ALTER COLLATION test1 RENAME TO test11;
1014 ALTER COLLATION test0 RENAME TO test11; -- fail
1015 ERROR: collation "test11" already exists in schema "collate_tests"
1016 ALTER COLLATION test1 RENAME TO test22; -- fail
1017 ERROR: collation "test1" for encoding "UTF8" does not exist
1018 ALTER COLLATION test11 OWNER TO regress_test_role;
1019 ALTER COLLATION test11 OWNER TO nonsense;
1020 ERROR: role "nonsense" does not exist
1021 ALTER COLLATION test11 SET SCHEMA test_schema;
1022 COMMENT ON COLLATION test0 IS 'US English';
1023 SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation')
1024 FROM pg_collation JOIN pg_namespace ON (collnamespace = pg_namespace.oid)
1025 WHERE collname LIKE 'test%'
1027 collname | nspname | obj_description
1028 ----------+---------------+-----------------
1029 test0 | collate_tests | US English
1030 test11 | test_schema |
1031 test5 | collate_tests |
1034 DROP COLLATION test0, test_schema.test11, test5;
1035 DROP COLLATION test0; -- fail
1036 ERROR: collation "test0" for encoding "UTF8" does not exist
1037 DROP COLLATION IF EXISTS test0;
1038 NOTICE: collation "test0" does not exist, skipping
1039 SELECT collname FROM pg_collation WHERE collname LIKE 'test%';
1044 DROP SCHEMA test_schema;
1045 DROP ROLE regress_test_role;
1047 ALTER COLLATION "en-x-icu" REFRESH VERSION;
1048 NOTICE: version has not changed
1050 CREATE COLLATION test0 FROM "C";
1051 CREATE TABLE collate_dep_test1 (a int, b text COLLATE test0);
1052 CREATE DOMAIN collate_dep_dom1 AS text COLLATE test0;
1053 CREATE TYPE collate_dep_test2 AS (x int, y text COLLATE test0);
1054 CREATE VIEW collate_dep_test3 AS SELECT text 'foo' COLLATE test0 AS foo;
1055 CREATE TABLE collate_dep_test4t (a int, b text);
1056 CREATE INDEX collate_dep_test4i ON collate_dep_test4t (b COLLATE test0);
1057 DROP COLLATION test0 RESTRICT; -- fail
1058 ERROR: cannot drop collation test0 because other objects depend on it
1059 DETAIL: column b of table collate_dep_test1 depends on collation test0
1060 type collate_dep_dom1 depends on collation test0
1061 column y of composite type collate_dep_test2 depends on collation test0
1062 view collate_dep_test3 depends on collation test0
1063 index collate_dep_test4i depends on collation test0
1064 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1065 DROP COLLATION test0 CASCADE;
1066 NOTICE: drop cascades to 5 other objects
1067 DETAIL: drop cascades to column b of table collate_dep_test1
1068 drop cascades to type collate_dep_dom1
1069 drop cascades to column y of composite type collate_dep_test2
1070 drop cascades to view collate_dep_test3
1071 drop cascades to index collate_dep_test4i
1072 \d collate_dep_test1
1073 Table "collate_tests.collate_dep_test1"
1074 Column | Type | Collation | Nullable | Default
1075 --------+---------+-----------+----------+---------
1078 \d collate_dep_test2
1079 Composite type "collate_tests.collate_dep_test2"
1080 Column | Type | Collation | Nullable | Default
1081 --------+---------+-----------+----------+---------
1084 DROP TABLE collate_dep_test1, collate_dep_test4t;
1085 DROP TYPE collate_dep_test2;
1086 -- test range types and collations
1087 create type textrange_c as range(subtype=text, collation="C");
1088 create type textrange_en_us as range(subtype=text, collation="en-x-icu");
1089 select textrange_c('A','Z') @> 'b'::text;
1095 select textrange_en_us('A','Z') @> 'b'::text;
1101 drop type textrange_c;
1102 drop type textrange_en_us;
1104 SET client_min_messages TO warning;
1105 DROP SCHEMA collate_tests CASCADE;
1107 -- leave a collation for pg_upgrade test
1108 CREATE COLLATION coll_icu_upgrade FROM "und-x-icu";