1/* 2 * This test is for Linux/glibc systems and assumes that a full set of 3 * locales is installed. It must be run in a database with UTF-8 encoding, 4 * because other encodings don't support all the characters used. 5 */ 6 7SET client_encoding TO UTF8; 8 9CREATE SCHEMA collate_tests; 10SET search_path = collate_tests; 11 12 13CREATE TABLE collate_test1 ( 14 a int, 15 b text COLLATE "en_US" NOT NULL 16); 17 18\d collate_test1 19 20CREATE TABLE collate_test_fail ( 21 a int, 22 b text COLLATE "ja_JP.eucjp" 23); 24 25CREATE TABLE collate_test_fail ( 26 a int, 27 b text COLLATE "foo" 28); 29 30CREATE TABLE collate_test_fail ( 31 a int COLLATE "en_US", 32 b text 33); 34 35CREATE TABLE collate_test_like ( 36 LIKE collate_test1 37); 38 39\d collate_test_like 40 41CREATE TABLE collate_test2 ( 42 a int, 43 b text COLLATE "sv_SE" 44); 45 46CREATE TABLE collate_test3 ( 47 a int, 48 b text COLLATE "C" 49); 50 51INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'äbc'), (3, 'bbc'), (4, 'ABC'); 52INSERT INTO collate_test2 SELECT * FROM collate_test1; 53INSERT INTO collate_test3 SELECT * FROM collate_test1; 54 55SELECT * FROM collate_test1 WHERE b >= 'bbc'; 56SELECT * FROM collate_test2 WHERE b >= 'bbc'; 57SELECT * FROM collate_test3 WHERE b >= 'bbc'; 58SELECT * FROM collate_test3 WHERE b >= 'BBC'; 59 60SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 61SELECT * FROM collate_test1 WHERE b >= 'bbc' COLLATE "C"; 62SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "C"; 63SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "en_US"; 64 65 66CREATE DOMAIN testdomain_sv AS text COLLATE "sv_SE"; 67CREATE DOMAIN testdomain_i AS int COLLATE "sv_SE"; -- fails 68CREATE TABLE collate_test4 ( 69 a int, 70 b testdomain_sv 71); 72INSERT INTO collate_test4 SELECT * FROM collate_test1; 73SELECT a, b FROM collate_test4 ORDER BY b; 74 75CREATE TABLE collate_test5 ( 76 a int, 77 b testdomain_sv COLLATE "en_US" 78); 79INSERT INTO collate_test5 SELECT * FROM collate_test1; 80SELECT a, b FROM collate_test5 ORDER BY b; 81 82 83SELECT a, b FROM collate_test1 ORDER BY b; 84SELECT a, b FROM collate_test2 ORDER BY b; 85SELECT a, b FROM collate_test3 ORDER BY b; 86 87SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 88 89-- star expansion 90SELECT * FROM collate_test1 ORDER BY b; 91SELECT * FROM collate_test2 ORDER BY b; 92SELECT * FROM collate_test3 ORDER BY b; 93 94-- constant expression folding 95SELECT 'bbc' COLLATE "en_US" > 'äbc' COLLATE "en_US" AS "true"; 96SELECT 'bbc' COLLATE "sv_SE" > 'äbc' COLLATE "sv_SE" AS "false"; 97 98-- upper/lower 99 100CREATE TABLE collate_test10 ( 101 a int, 102 x text COLLATE "en_US", 103 y text COLLATE "tr_TR" 104); 105 106INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); 107 108SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10; 109SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10; 110 111SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; 112 113-- LIKE/ILIKE 114 115SELECT * FROM collate_test1 WHERE b LIKE 'abc'; 116SELECT * FROM collate_test1 WHERE b LIKE 'abc%'; 117SELECT * FROM collate_test1 WHERE b LIKE '%bc%'; 118SELECT * FROM collate_test1 WHERE b ILIKE 'abc'; 119SELECT * FROM collate_test1 WHERE b ILIKE 'abc%'; 120SELECT * FROM collate_test1 WHERE b ILIKE '%bc%'; 121 122SELECT 'Türkiye' COLLATE "en_US" ILIKE '%KI%' AS "true"; 123SELECT 'Türkiye' COLLATE "tr_TR" ILIKE '%KI%' AS "false"; 124 125SELECT 'bıt' ILIKE 'BIT' COLLATE "en_US" AS "false"; 126SELECT 'bıt' ILIKE 'BIT' COLLATE "tr_TR" AS "true"; 127 128-- The following actually exercises the selectivity estimation for ILIKE. 129SELECT relname FROM pg_class WHERE relname ILIKE 'abc%'; 130 131-- regular expressions 132 133SELECT * FROM collate_test1 WHERE b ~ '^abc$'; 134SELECT * FROM collate_test1 WHERE b ~ '^abc'; 135SELECT * FROM collate_test1 WHERE b ~ 'bc'; 136SELECT * FROM collate_test1 WHERE b ~* '^abc$'; 137SELECT * FROM collate_test1 WHERE b ~* '^abc'; 138SELECT * FROM collate_test1 WHERE b ~* 'bc'; 139 140CREATE TABLE collate_test6 ( 141 a int, 142 b text COLLATE "en_US" 143); 144INSERT INTO collate_test6 VALUES (1, 'abc'), (2, 'ABC'), (3, '123'), (4, 'ab1'), 145 (5, 'a1!'), (6, 'a c'), (7, '!.;'), (8, ' '), 146 (9, 'äbç'), (10, 'ÄBÇ'); 147SELECT b, 148 b ~ '^[[:alpha:]]+$' AS is_alpha, 149 b ~ '^[[:upper:]]+$' AS is_upper, 150 b ~ '^[[:lower:]]+$' AS is_lower, 151 b ~ '^[[:digit:]]+$' AS is_digit, 152 b ~ '^[[:alnum:]]+$' AS is_alnum, 153 b ~ '^[[:graph:]]+$' AS is_graph, 154 b ~ '^[[:print:]]+$' AS is_print, 155 b ~ '^[[:punct:]]+$' AS is_punct, 156 b ~ '^[[:space:]]+$' AS is_space 157FROM collate_test6; 158 159SELECT 'Türkiye' COLLATE "en_US" ~* 'KI' AS "true"; 160SELECT 'Türkiye' COLLATE "tr_TR" ~* 'KI' AS "false"; 161 162SELECT 'bıt' ~* 'BIT' COLLATE "en_US" AS "false"; 163SELECT 'bıt' ~* 'BIT' COLLATE "tr_TR" AS "true"; 164 165-- The following actually exercises the selectivity estimation for ~*. 166SELECT relname FROM pg_class WHERE relname ~* '^abc'; 167 168 169-- to_char 170 171SET lc_time TO 'tr_TR'; 172SELECT to_char(date '2010-02-01', 'DD TMMON YYYY'); 173SELECT to_char(date '2010-02-01', 'DD TMMON YYYY' COLLATE "tr_TR"); 174SELECT to_char(date '2010-04-01', 'DD TMMON YYYY'); 175SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR"); 176 177 178-- backwards parsing 179 180CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 181CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 182CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "C") FROM collate_test10; 183 184SELECT table_name, view_definition FROM information_schema.views 185 WHERE table_name LIKE 'collview%' ORDER BY 1; 186 187 188-- collation propagation in various expression types 189 190SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; 191SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; 192SELECT a, coalesce(b, 'foo') FROM collate_test3 ORDER BY 2; 193SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10; 194 195SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; 196SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; 197SELECT a, b, greatest(b, 'CCC') FROM collate_test3 ORDER BY 3; 198SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10; 199 200SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; 201SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; 202SELECT a, nullif(b, 'abc') FROM collate_test3 ORDER BY 2; 203SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10; 204 205SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; 206SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; 207SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test3 ORDER BY 2; 208 209CREATE DOMAIN testdomain AS text; 210SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; 211SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; 212SELECT a, b::testdomain FROM collate_test3 ORDER BY 2; 213SELECT a, b::testdomain_sv FROM collate_test3 ORDER BY 2; 214SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10; 215 216SELECT min(b), max(b) FROM collate_test1; 217SELECT min(b), max(b) FROM collate_test2; 218SELECT min(b), max(b) FROM collate_test3; 219 220SELECT array_agg(b ORDER BY b) FROM collate_test1; 221SELECT array_agg(b ORDER BY b) FROM collate_test2; 222SELECT array_agg(b ORDER BY b) FROM collate_test3; 223 224SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; 225SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; 226SELECT a, b FROM collate_test3 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test3 WHERE a > 1 ORDER BY 2; 227SELECT a, b FROM collate_test3 EXCEPT SELECT a, b FROM collate_test3 WHERE a < 2 ORDER BY 2; 228 229SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 230SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- ok 231SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 232SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- ok 233SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 234SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 235 236CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail 237 238-- ideally this would be a parse-time error, but for now it must be run-time: 239select x < y from collate_test10; -- fail 240select x || y from collate_test10; -- ok, because || is not collation aware 241select x, y from collate_test10 order by x || y; -- not so ok 242 243-- collation mismatch between recursive and non-recursive term 244WITH RECURSIVE foo(x) AS 245 (SELECT x FROM (VALUES('a' COLLATE "en_US"),('b')) t(x) 246 UNION ALL 247 SELECT (x || 'c') COLLATE "de_DE" FROM foo WHERE length(x) < 10) 248SELECT * FROM foo; 249 250 251-- casting 252 253SELECT CAST('42' AS text COLLATE "C"); 254 255SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; 256SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; 257SELECT a, CAST(b AS varchar) FROM collate_test3 ORDER BY 2; 258 259 260-- propagation of collation in SQL functions (inlined and non-inlined cases) 261-- and plpgsql functions too 262 263CREATE FUNCTION mylt (text, text) RETURNS boolean LANGUAGE sql 264 AS $$ select $1 < $2 $$; 265 266CREATE FUNCTION mylt_noninline (text, text) RETURNS boolean LANGUAGE sql 267 AS $$ select $1 < $2 limit 1 $$; 268 269CREATE FUNCTION mylt_plpgsql (text, text) RETURNS boolean LANGUAGE plpgsql 270 AS $$ begin return $1 < $2; end $$; 271 272SELECT a.b AS a, b.b AS b, a.b < b.b AS lt, 273 mylt(a.b, b.b), mylt_noninline(a.b, b.b), mylt_plpgsql(a.b, b.b) 274FROM collate_test1 a, collate_test1 b 275ORDER BY a.b, b.b; 276 277SELECT a.b AS a, b.b AS b, a.b < b.b COLLATE "C" AS lt, 278 mylt(a.b, b.b COLLATE "C"), mylt_noninline(a.b, b.b COLLATE "C"), 279 mylt_plpgsql(a.b, b.b COLLATE "C") 280FROM collate_test1 a, collate_test1 b 281ORDER BY a.b, b.b; 282 283 284-- collation override in plpgsql 285 286CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ 287declare 288 xx text := x; 289 yy text := y; 290begin 291 return xx < yy; 292end 293$$; 294 295SELECT mylt2('a', 'B' collate "en_US") as t, mylt2('a', 'B' collate "C") as f; 296 297CREATE OR REPLACE FUNCTION 298 mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ 299declare 300 xx text COLLATE "POSIX" := x; 301 yy text := y; 302begin 303 return xx < yy; 304end 305$$; 306 307SELECT mylt2('a', 'B') as f; 308SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations 309SELECT mylt2('a', 'B' collate "POSIX") as f; 310 311 312-- polymorphism 313 314SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; 315SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; 316SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test3)) ORDER BY 1; 317 318CREATE FUNCTION dup (anyelement) RETURNS anyelement 319 AS 'select $1' LANGUAGE sql; 320 321SELECT a, dup(b) FROM collate_test1 ORDER BY 2; 322SELECT a, dup(b) FROM collate_test2 ORDER BY 2; 323SELECT a, dup(b) FROM collate_test3 ORDER BY 2; 324 325 326-- indexes 327 328CREATE INDEX collate_test1_idx1 ON collate_test1 (b); 329CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "C"); 330CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "C")); -- this is different grammatically 331CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); 332 333CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "C"); -- fail 334CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail 335 336SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; 337 338 339-- schema manipulation commands 340 341CREATE ROLE regress_test_role; 342CREATE SCHEMA test_schema; 343 344-- We need to do this this way to cope with varying names for encodings: 345do $$ 346BEGIN 347 EXECUTE 'CREATE COLLATION test0 (locale = ' || 348 quote_literal(current_setting('lc_collate')) || ');'; 349END 350$$; 351CREATE COLLATION test0 FROM "C"; -- fail, duplicate name 352CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped 353CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped 354do $$ 355BEGIN 356 EXECUTE 'CREATE COLLATION test1 (lc_collate = ' || 357 quote_literal(current_setting('lc_collate')) || 358 ', lc_ctype = ' || 359 quote_literal(current_setting('lc_ctype')) || ');'; 360END 361$$; 362CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype 363CREATE COLLATION testx (locale = 'nonsense'); -- fail 364CREATE COLLATION testy (locale = 'en_US.utf8', version = 'foo'); -- fail, no versions for libc 365 366CREATE COLLATION test4 FROM nonsense; 367CREATE COLLATION test5 FROM test0; 368 369SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1; 370 371ALTER COLLATION test1 RENAME TO test11; 372ALTER COLLATION test0 RENAME TO test11; -- fail 373ALTER COLLATION test1 RENAME TO test22; -- fail 374 375ALTER COLLATION test11 OWNER TO regress_test_role; 376ALTER COLLATION test11 OWNER TO nonsense; 377ALTER COLLATION test11 SET SCHEMA test_schema; 378 379COMMENT ON COLLATION test0 IS 'US English'; 380 381SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation') 382 FROM pg_collation JOIN pg_namespace ON (collnamespace = pg_namespace.oid) 383 WHERE collname LIKE 'test%' 384 ORDER BY 1; 385 386DROP COLLATION test0, test_schema.test11, test5; 387DROP COLLATION test0; -- fail 388DROP COLLATION IF EXISTS test0; 389 390SELECT collname FROM pg_collation WHERE collname LIKE 'test%'; 391 392DROP SCHEMA test_schema; 393DROP ROLE regress_test_role; 394 395 396-- ALTER 397 398ALTER COLLATION "en_US" REFRESH VERSION; 399 400 401-- dependencies 402 403CREATE COLLATION test0 FROM "C"; 404 405CREATE TABLE collate_dep_test1 (a int, b text COLLATE test0); 406CREATE DOMAIN collate_dep_dom1 AS text COLLATE test0; 407CREATE TYPE collate_dep_test2 AS (x int, y text COLLATE test0); 408CREATE VIEW collate_dep_test3 AS SELECT text 'foo' COLLATE test0 AS foo; 409CREATE TABLE collate_dep_test4t (a int, b text); 410CREATE INDEX collate_dep_test4i ON collate_dep_test4t (b COLLATE test0); 411 412DROP COLLATION test0 RESTRICT; -- fail 413DROP COLLATION test0 CASCADE; 414 415\d collate_dep_test1 416\d collate_dep_test2 417 418DROP TABLE collate_dep_test1, collate_dep_test4t; 419DROP TYPE collate_dep_test2; 420 421-- test range types and collations 422 423create type textrange_c as range(subtype=text, collation="C"); 424create type textrange_en_us as range(subtype=text, collation="en_US"); 425 426select textrange_c('A','Z') @> 'b'::text; 427select textrange_en_us('A','Z') @> 'b'::text; 428 429drop type textrange_c; 430drop type textrange_en_us; 431 432 433-- cleanup 434DROP SCHEMA collate_tests CASCADE; 435