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 */ 6SET client_encoding TO UTF8; 7CREATE SCHEMA collate_tests; 8SET search_path = collate_tests; 9CREATE TABLE collate_test1 ( 10 a int, 11 b text COLLATE "en_US" NOT NULL 12); 13\d collate_test1 14 Table "collate_tests.collate_test1" 15 Column | Type | Collation | Nullable | Default 16--------+---------+-----------+----------+--------- 17 a | integer | | | 18 b | text | en_US | not null | 19 20CREATE TABLE collate_test_fail ( 21 a int, 22 b text COLLATE "ja_JP.eucjp" 23); 24ERROR: collation "ja_JP.eucjp" for encoding "UTF8" does not exist 25LINE 3: b text COLLATE "ja_JP.eucjp" 26 ^ 27CREATE TABLE collate_test_fail ( 28 a int, 29 b text COLLATE "foo" 30); 31ERROR: collation "foo" for encoding "UTF8" does not exist 32LINE 3: b text COLLATE "foo" 33 ^ 34CREATE TABLE collate_test_fail ( 35 a int COLLATE "en_US", 36 b text 37); 38ERROR: collations are not supported by type integer 39LINE 2: a int COLLATE "en_US", 40 ^ 41CREATE TABLE collate_test_like ( 42 LIKE collate_test1 43); 44\d collate_test_like 45 Table "collate_tests.collate_test_like" 46 Column | Type | Collation | Nullable | Default 47--------+---------+-----------+----------+--------- 48 a | integer | | | 49 b | text | en_US | not null | 50 51CREATE TABLE collate_test2 ( 52 a int, 53 b text COLLATE "sv_SE" 54); 55CREATE TABLE collate_test3 ( 56 a int, 57 b text COLLATE "C" 58); 59INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'äbc'), (3, 'bbc'), (4, 'ABC'); 60INSERT INTO collate_test2 SELECT * FROM collate_test1; 61INSERT INTO collate_test3 SELECT * FROM collate_test1; 62SELECT * FROM collate_test1 WHERE b >= 'bbc'; 63 a | b 64---+----- 65 3 | bbc 66(1 row) 67 68SELECT * FROM collate_test2 WHERE b >= 'bbc'; 69 a | b 70---+----- 71 2 | äbc 72 3 | bbc 73(2 rows) 74 75SELECT * FROM collate_test3 WHERE b >= 'bbc'; 76 a | b 77---+----- 78 2 | äbc 79 3 | bbc 80(2 rows) 81 82SELECT * FROM collate_test3 WHERE b >= 'BBC'; 83 a | b 84---+----- 85 1 | abc 86 2 | äbc 87 3 | bbc 88(3 rows) 89 90SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 91 a | b 92---+----- 93 2 | äbc 94 3 | bbc 95(2 rows) 96 97SELECT * FROM collate_test1 WHERE b >= 'bbc' COLLATE "C"; 98 a | b 99---+----- 100 2 | äbc 101 3 | bbc 102(2 rows) 103 104SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "C"; 105 a | b 106---+----- 107 2 | äbc 108 3 | bbc 109(2 rows) 110 111SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "en_US"; 112ERROR: collation mismatch between explicit collations "C" and "en_US" 113LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "e... 114 ^ 115CREATE DOMAIN testdomain_sv AS text COLLATE "sv_SE"; 116CREATE DOMAIN testdomain_i AS int COLLATE "sv_SE"; -- fails 117ERROR: collations are not supported by type integer 118CREATE TABLE collate_test4 ( 119 a int, 120 b testdomain_sv 121); 122INSERT INTO collate_test4 SELECT * FROM collate_test1; 123SELECT a, b FROM collate_test4 ORDER BY b; 124 a | b 125---+----- 126 1 | abc 127 4 | ABC 128 3 | bbc 129 2 | äbc 130(4 rows) 131 132CREATE TABLE collate_test5 ( 133 a int, 134 b testdomain_sv COLLATE "en_US" 135); 136INSERT INTO collate_test5 SELECT * FROM collate_test1; 137SELECT a, b FROM collate_test5 ORDER BY b; 138 a | b 139---+----- 140 1 | abc 141 4 | ABC 142 2 | äbc 143 3 | bbc 144(4 rows) 145 146SELECT a, b FROM collate_test1 ORDER BY b; 147 a | b 148---+----- 149 1 | abc 150 4 | ABC 151 2 | äbc 152 3 | bbc 153(4 rows) 154 155SELECT a, b FROM collate_test2 ORDER BY b; 156 a | b 157---+----- 158 1 | abc 159 4 | ABC 160 3 | bbc 161 2 | äbc 162(4 rows) 163 164SELECT a, b FROM collate_test3 ORDER BY b; 165 a | b 166---+----- 167 4 | ABC 168 1 | abc 169 3 | bbc 170 2 | äbc 171(4 rows) 172 173SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 174 a | b 175---+----- 176 4 | ABC 177 1 | abc 178 3 | bbc 179 2 | äbc 180(4 rows) 181 182-- star expansion 183SELECT * FROM collate_test1 ORDER BY b; 184 a | b 185---+----- 186 1 | abc 187 4 | ABC 188 2 | äbc 189 3 | bbc 190(4 rows) 191 192SELECT * FROM collate_test2 ORDER BY b; 193 a | b 194---+----- 195 1 | abc 196 4 | ABC 197 3 | bbc 198 2 | äbc 199(4 rows) 200 201SELECT * FROM collate_test3 ORDER BY b; 202 a | b 203---+----- 204 4 | ABC 205 1 | abc 206 3 | bbc 207 2 | äbc 208(4 rows) 209 210-- constant expression folding 211SELECT 'bbc' COLLATE "en_US" > 'äbc' COLLATE "en_US" AS "true"; 212 true 213------ 214 t 215(1 row) 216 217SELECT 'bbc' COLLATE "sv_SE" > 'äbc' COLLATE "sv_SE" AS "false"; 218 false 219------- 220 f 221(1 row) 222 223-- upper/lower 224CREATE TABLE collate_test10 ( 225 a int, 226 x text COLLATE "en_US", 227 y text COLLATE "tr_TR" 228); 229INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); 230SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10; 231 a | lower | lower | upper | upper | initcap | initcap 232---+-------+-------+-------+-------+---------+--------- 233 1 | hij | hij | HIJ | HİJ | Hij | Hij 234 2 | hij | hıj | HIJ | HIJ | Hij | Hıj 235(2 rows) 236 237SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10; 238 a | lower | lower 239---+-------+------- 240 1 | hij | hij 241 2 | hij | hij 242(2 rows) 243 244SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; 245 a | x | y 246---+-----+----- 247 2 | HIJ | HIJ 248 1 | hij | hij 249(2 rows) 250 251-- LIKE/ILIKE 252SELECT * FROM collate_test1 WHERE b LIKE 'abc'; 253 a | b 254---+----- 255 1 | abc 256(1 row) 257 258SELECT * FROM collate_test1 WHERE b LIKE 'abc%'; 259 a | b 260---+----- 261 1 | abc 262(1 row) 263 264SELECT * FROM collate_test1 WHERE b LIKE '%bc%'; 265 a | b 266---+----- 267 1 | abc 268 2 | äbc 269 3 | bbc 270(3 rows) 271 272SELECT * FROM collate_test1 WHERE b ILIKE 'abc'; 273 a | b 274---+----- 275 1 | abc 276 4 | ABC 277(2 rows) 278 279SELECT * FROM collate_test1 WHERE b ILIKE 'abc%'; 280 a | b 281---+----- 282 1 | abc 283 4 | ABC 284(2 rows) 285 286SELECT * FROM collate_test1 WHERE b ILIKE '%bc%'; 287 a | b 288---+----- 289 1 | abc 290 2 | äbc 291 3 | bbc 292 4 | ABC 293(4 rows) 294 295SELECT 'Türkiye' COLLATE "en_US" ILIKE '%KI%' AS "true"; 296 true 297------ 298 t 299(1 row) 300 301SELECT 'Türkiye' COLLATE "tr_TR" ILIKE '%KI%' AS "false"; 302 false 303------- 304 f 305(1 row) 306 307SELECT 'bıt' ILIKE 'BIT' COLLATE "en_US" AS "false"; 308 false 309------- 310 f 311(1 row) 312 313SELECT 'bıt' ILIKE 'BIT' COLLATE "tr_TR" AS "true"; 314 true 315------ 316 t 317(1 row) 318 319-- The following actually exercises the selectivity estimation for ILIKE. 320SELECT relname FROM pg_class WHERE relname ILIKE 'abc%'; 321 relname 322--------- 323(0 rows) 324 325-- regular expressions 326SELECT * FROM collate_test1 WHERE b ~ '^abc$'; 327 a | b 328---+----- 329 1 | abc 330(1 row) 331 332SELECT * FROM collate_test1 WHERE b ~ '^abc'; 333 a | b 334---+----- 335 1 | abc 336(1 row) 337 338SELECT * FROM collate_test1 WHERE b ~ 'bc'; 339 a | b 340---+----- 341 1 | abc 342 2 | äbc 343 3 | bbc 344(3 rows) 345 346SELECT * FROM collate_test1 WHERE b ~* '^abc$'; 347 a | b 348---+----- 349 1 | abc 350 4 | ABC 351(2 rows) 352 353SELECT * FROM collate_test1 WHERE b ~* '^abc'; 354 a | b 355---+----- 356 1 | abc 357 4 | ABC 358(2 rows) 359 360SELECT * FROM collate_test1 WHERE b ~* 'bc'; 361 a | b 362---+----- 363 1 | abc 364 2 | äbc 365 3 | bbc 366 4 | ABC 367(4 rows) 368 369CREATE TABLE collate_test6 ( 370 a int, 371 b text COLLATE "en_US" 372); 373INSERT INTO collate_test6 VALUES (1, 'abc'), (2, 'ABC'), (3, '123'), (4, 'ab1'), 374 (5, 'a1!'), (6, 'a c'), (7, '!.;'), (8, ' '), 375 (9, 'äbç'), (10, 'ÄBÇ'); 376SELECT b, 377 b ~ '^[[:alpha:]]+$' AS is_alpha, 378 b ~ '^[[:upper:]]+$' AS is_upper, 379 b ~ '^[[:lower:]]+$' AS is_lower, 380 b ~ '^[[:digit:]]+$' AS is_digit, 381 b ~ '^[[:alnum:]]+$' AS is_alnum, 382 b ~ '^[[:graph:]]+$' AS is_graph, 383 b ~ '^[[:print:]]+$' AS is_print, 384 b ~ '^[[:punct:]]+$' AS is_punct, 385 b ~ '^[[:space:]]+$' AS is_space 386FROM collate_test6; 387 b | is_alpha | is_upper | is_lower | is_digit | is_alnum | is_graph | is_print | is_punct | is_space 388-----+----------+----------+----------+----------+----------+----------+----------+----------+---------- 389 abc | t | f | t | f | t | t | t | f | f 390 ABC | t | t | f | f | t | t | t | f | f 391 123 | f | f | f | t | t | t | t | f | f 392 ab1 | f | f | f | f | t | t | t | f | f 393 a1! | f | f | f | f | f | t | t | f | f 394 a c | f | f | f | f | f | f | t | f | f 395 !.; | f | f | f | f | f | t | t | t | f 396 | f | f | f | f | f | f | t | f | t 397 äbç | t | f | t | f | t | t | t | f | f 398 ÄBÇ | t | t | f | f | t | t | t | f | f 399(10 rows) 400 401SELECT 'Türkiye' COLLATE "en_US" ~* 'KI' AS "true"; 402 true 403------ 404 t 405(1 row) 406 407SELECT 'Türkiye' COLLATE "tr_TR" ~* 'KI' AS "false"; 408 false 409------- 410 f 411(1 row) 412 413SELECT 'bıt' ~* 'BIT' COLLATE "en_US" AS "false"; 414 false 415------- 416 f 417(1 row) 418 419SELECT 'bıt' ~* 'BIT' COLLATE "tr_TR" AS "true"; 420 true 421------ 422 t 423(1 row) 424 425-- The following actually exercises the selectivity estimation for ~*. 426SELECT relname FROM pg_class WHERE relname ~* '^abc'; 427 relname 428--------- 429(0 rows) 430 431-- to_char 432SET lc_time TO 'tr_TR'; 433SELECT to_char(date '2010-02-01', 'DD TMMON YYYY'); 434 to_char 435------------- 436 01 ŞUB 2010 437(1 row) 438 439SELECT to_char(date '2010-02-01', 'DD TMMON YYYY' COLLATE "tr_TR"); 440 to_char 441------------- 442 01 ŞUB 2010 443(1 row) 444 445SELECT to_char(date '2010-04-01', 'DD TMMON YYYY'); 446 to_char 447------------- 448 01 NIS 2010 449(1 row) 450 451SELECT to_char(date '2010-04-01', 'DD TMMON YYYY' COLLATE "tr_TR"); 452 to_char 453------------- 454 01 NİS 2010 455(1 row) 456 457-- backwards parsing 458CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 459CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 460CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "C") FROM collate_test10; 461SELECT table_name, view_definition FROM information_schema.views 462 WHERE table_name LIKE 'collview%' ORDER BY 1; 463 table_name | view_definition 464------------+-------------------------------------------------------------------------- 465 collview1 | SELECT collate_test1.a, + 466 | collate_test1.b + 467 | FROM collate_test1 + 468 | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); 469 collview2 | SELECT collate_test1.a, + 470 | collate_test1.b + 471 | FROM collate_test1 + 472 | ORDER BY (collate_test1.b COLLATE "C"); 473 collview3 | SELECT collate_test10.a, + 474 | lower(((collate_test10.x || collate_test10.x) COLLATE "C")) AS lower+ 475 | FROM collate_test10; 476(3 rows) 477 478-- collation propagation in various expression types 479SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; 480 a | coalesce 481---+---------- 482 1 | abc 483 4 | ABC 484 2 | äbc 485 3 | bbc 486(4 rows) 487 488SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; 489 a | coalesce 490---+---------- 491 1 | abc 492 4 | ABC 493 3 | bbc 494 2 | äbc 495(4 rows) 496 497SELECT a, coalesce(b, 'foo') FROM collate_test3 ORDER BY 2; 498 a | coalesce 499---+---------- 500 4 | ABC 501 1 | abc 502 3 | bbc 503 2 | äbc 504(4 rows) 505 506SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10; 507 a | lower | lower 508---+-------+------- 509 1 | hij | hij 510 2 | hij | hıj 511(2 rows) 512 513SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; 514 a | b | greatest 515---+-----+---------- 516 1 | abc | CCC 517 2 | äbc | CCC 518 3 | bbc | CCC 519 4 | ABC | CCC 520(4 rows) 521 522SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; 523 a | b | greatest 524---+-----+---------- 525 1 | abc | CCC 526 3 | bbc | CCC 527 4 | ABC | CCC 528 2 | äbc | äbc 529(4 rows) 530 531SELECT a, b, greatest(b, 'CCC') FROM collate_test3 ORDER BY 3; 532 a | b | greatest 533---+-----+---------- 534 4 | ABC | CCC 535 1 | abc | abc 536 3 | bbc | bbc 537 2 | äbc | äbc 538(4 rows) 539 540SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10; 541 a | x | y | lower | lower 542---+-----+-----+-------+------- 543 1 | hij | hij | hij | hij 544 2 | HIJ | HIJ | hij | hıj 545(2 rows) 546 547SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; 548 a | nullif 549---+-------- 550 4 | ABC 551 2 | äbc 552 3 | bbc 553 1 | 554(4 rows) 555 556SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; 557 a | nullif 558---+-------- 559 4 | ABC 560 3 | bbc 561 2 | äbc 562 1 | 563(4 rows) 564 565SELECT a, nullif(b, 'abc') FROM collate_test3 ORDER BY 2; 566 a | nullif 567---+-------- 568 4 | ABC 569 3 | bbc 570 2 | äbc 571 1 | 572(4 rows) 573 574SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10; 575 a | lower | lower 576---+-------+------- 577 1 | hij | hij 578 2 | hij | hıj 579(2 rows) 580 581SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; 582 a | b 583---+------ 584 4 | ABC 585 2 | äbc 586 1 | abcd 587 3 | bbc 588(4 rows) 589 590SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; 591 a | b 592---+------ 593 4 | ABC 594 1 | abcd 595 3 | bbc 596 2 | äbc 597(4 rows) 598 599SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test3 ORDER BY 2; 600 a | b 601---+------ 602 4 | ABC 603 1 | abcd 604 3 | bbc 605 2 | äbc 606(4 rows) 607 608CREATE DOMAIN testdomain AS text; 609SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; 610 a | b 611---+----- 612 1 | abc 613 4 | ABC 614 2 | äbc 615 3 | bbc 616(4 rows) 617 618SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; 619 a | b 620---+----- 621 1 | abc 622 4 | ABC 623 3 | bbc 624 2 | äbc 625(4 rows) 626 627SELECT a, b::testdomain FROM collate_test3 ORDER BY 2; 628 a | b 629---+----- 630 4 | ABC 631 1 | abc 632 3 | bbc 633 2 | äbc 634(4 rows) 635 636SELECT a, b::testdomain_sv FROM collate_test3 ORDER BY 2; 637 a | b 638---+----- 639 1 | abc 640 4 | ABC 641 3 | bbc 642 2 | äbc 643(4 rows) 644 645SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10; 646 a | lower | lower 647---+-------+------- 648 1 | hij | hij 649 2 | hij | hıj 650(2 rows) 651 652SELECT min(b), max(b) FROM collate_test1; 653 min | max 654-----+----- 655 abc | bbc 656(1 row) 657 658SELECT min(b), max(b) FROM collate_test2; 659 min | max 660-----+----- 661 abc | äbc 662(1 row) 663 664SELECT min(b), max(b) FROM collate_test3; 665 min | max 666-----+----- 667 ABC | äbc 668(1 row) 669 670SELECT array_agg(b ORDER BY b) FROM collate_test1; 671 array_agg 672------------------- 673 {abc,ABC,äbc,bbc} 674(1 row) 675 676SELECT array_agg(b ORDER BY b) FROM collate_test2; 677 array_agg 678------------------- 679 {abc,ABC,bbc,äbc} 680(1 row) 681 682SELECT array_agg(b ORDER BY b) FROM collate_test3; 683 array_agg 684------------------- 685 {ABC,abc,bbc,äbc} 686(1 row) 687 688SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; 689 a | b 690---+----- 691 1 | abc 692 1 | abc 693 4 | ABC 694 4 | ABC 695 2 | äbc 696 2 | äbc 697 3 | bbc 698 3 | bbc 699(8 rows) 700 701SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; 702 a | b 703---+----- 704 1 | abc 705 4 | ABC 706 3 | bbc 707 2 | äbc 708(4 rows) 709 710SELECT a, b FROM collate_test3 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test3 WHERE a > 1 ORDER BY 2; 711 a | b 712---+----- 713 3 | bbc 714 2 | äbc 715(2 rows) 716 717SELECT a, b FROM collate_test3 EXCEPT SELECT a, b FROM collate_test3 WHERE a < 2 ORDER BY 2; 718 a | b 719---+----- 720 4 | ABC 721 3 | bbc 722 2 | äbc 723(3 rows) 724 725SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 726ERROR: could not determine which collation to use for string comparison 727HINT: Use the COLLATE clause to set the collation explicitly. 728SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- ok 729 a | b 730---+----- 731 1 | abc 732 2 | äbc 733 3 | bbc 734 4 | ABC 735 1 | abc 736 2 | äbc 737 3 | bbc 738 4 | ABC 739(8 rows) 740 741SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 742ERROR: collation mismatch between implicit collations "en_US" and "C" 743LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat... 744 ^ 745HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 746SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test3 ORDER BY 2; -- ok 747 a | b 748---+----- 749 4 | ABC 750 1 | abc 751 3 | bbc 752 2 | äbc 753(4 rows) 754 755SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 756ERROR: collation mismatch between implicit collations "en_US" and "C" 757LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col... 758 ^ 759HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 760SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test3 ORDER BY 2; -- fail 761ERROR: collation mismatch between implicit collations "en_US" and "C" 762LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla... 763 ^ 764HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 765CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test3; -- fail 766ERROR: no collation was derived for column "b" with collatable type text 767HINT: Use the COLLATE clause to set the collation explicitly. 768-- ideally this would be a parse-time error, but for now it must be run-time: 769select x < y from collate_test10; -- fail 770ERROR: could not determine which collation to use for string comparison 771HINT: Use the COLLATE clause to set the collation explicitly. 772select x || y from collate_test10; -- ok, because || is not collation aware 773 ?column? 774---------- 775 hijhij 776 HIJHIJ 777(2 rows) 778 779select x, y from collate_test10 order by x || y; -- not so ok 780ERROR: collation mismatch between implicit collations "en_US" and "tr_TR" 781LINE 1: select x, y from collate_test10 order by x || y; 782 ^ 783HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 784-- collation mismatch between recursive and non-recursive term 785WITH RECURSIVE foo(x) AS 786 (SELECT x FROM (VALUES('a' COLLATE "en_US"),('b')) t(x) 787 UNION ALL 788 SELECT (x || 'c') COLLATE "de_DE" FROM foo WHERE length(x) < 10) 789SELECT * FROM foo; 790ERROR: recursive query "foo" column 1 has collation "en_US" in non-recursive term but collation "de_DE" overall 791LINE 2: (SELECT x FROM (VALUES('a' COLLATE "en_US"),('b')) t(x) 792 ^ 793HINT: Use the COLLATE clause to set the collation of the non-recursive term. 794-- casting 795SELECT CAST('42' AS text COLLATE "C"); 796ERROR: syntax error at or near "COLLATE" 797LINE 1: SELECT CAST('42' AS text COLLATE "C"); 798 ^ 799SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; 800 a | b 801---+----- 802 1 | abc 803 4 | ABC 804 2 | äbc 805 3 | bbc 806(4 rows) 807 808SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; 809 a | b 810---+----- 811 1 | abc 812 4 | ABC 813 3 | bbc 814 2 | äbc 815(4 rows) 816 817SELECT a, CAST(b AS varchar) FROM collate_test3 ORDER BY 2; 818 a | b 819---+----- 820 4 | ABC 821 1 | abc 822 3 | bbc 823 2 | äbc 824(4 rows) 825 826-- propagation of collation in SQL functions (inlined and non-inlined cases) 827-- and plpgsql functions too 828CREATE FUNCTION mylt (text, text) RETURNS boolean LANGUAGE sql 829 AS $$ select $1 < $2 $$; 830CREATE FUNCTION mylt_noninline (text, text) RETURNS boolean LANGUAGE sql 831 AS $$ select $1 < $2 limit 1 $$; 832CREATE FUNCTION mylt_plpgsql (text, text) RETURNS boolean LANGUAGE plpgsql 833 AS $$ begin return $1 < $2; end $$; 834SELECT a.b AS a, b.b AS b, a.b < b.b AS lt, 835 mylt(a.b, b.b), mylt_noninline(a.b, b.b), mylt_plpgsql(a.b, b.b) 836FROM collate_test1 a, collate_test1 b 837ORDER BY a.b, b.b; 838 a | b | lt | mylt | mylt_noninline | mylt_plpgsql 839-----+-----+----+------+----------------+-------------- 840 abc | abc | f | f | f | f 841 abc | ABC | t | t | t | t 842 abc | äbc | t | t | t | t 843 abc | bbc | t | t | t | t 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 äbc | abc | f | f | f | f 849 äbc | ABC | f | f | f | f 850 äbc | äbc | f | f | f | f 851 äbc | bbc | t | t | t | t 852 bbc | abc | f | f | f | f 853 bbc | ABC | f | f | f | f 854 bbc | äbc | f | f | f | f 855 bbc | bbc | f | f | f | f 856(16 rows) 857 858SELECT a.b AS a, b.b AS b, a.b < b.b COLLATE "C" AS lt, 859 mylt(a.b, b.b COLLATE "C"), mylt_noninline(a.b, b.b COLLATE "C"), 860 mylt_plpgsql(a.b, b.b COLLATE "C") 861FROM collate_test1 a, collate_test1 b 862ORDER BY a.b, b.b; 863 a | b | lt | mylt | mylt_noninline | mylt_plpgsql 864-----+-----+----+------+----------------+-------------- 865 abc | abc | f | f | f | f 866 abc | ABC | f | f | f | f 867 abc | äbc | t | t | t | t 868 abc | bbc | t | t | t | t 869 ABC | abc | t | t | t | t 870 ABC | ABC | f | f | f | f 871 ABC | äbc | t | t | t | t 872 ABC | bbc | t | t | t | t 873 äbc | abc | f | f | f | f 874 äbc | ABC | f | f | f | f 875 äbc | äbc | f | f | f | f 876 äbc | bbc | f | f | f | f 877 bbc | abc | f | f | f | f 878 bbc | ABC | f | f | f | f 879 bbc | äbc | t | t | t | t 880 bbc | bbc | f | f | f | f 881(16 rows) 882 883-- collation override in plpgsql 884CREATE FUNCTION mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ 885declare 886 xx text := x; 887 yy text := y; 888begin 889 return xx < yy; 890end 891$$; 892SELECT mylt2('a', 'B' collate "en_US") as t, mylt2('a', 'B' collate "C") as f; 893 t | f 894---+--- 895 t | f 896(1 row) 897 898CREATE OR REPLACE FUNCTION 899 mylt2 (x text, y text) RETURNS boolean LANGUAGE plpgsql AS $$ 900declare 901 xx text COLLATE "POSIX" := x; 902 yy text := y; 903begin 904 return xx < yy; 905end 906$$; 907SELECT mylt2('a', 'B') as f; 908 f 909--- 910 f 911(1 row) 912 913SELECT mylt2('a', 'B' collate "C") as fail; -- conflicting collations 914ERROR: could not determine which collation to use for string comparison 915HINT: Use the COLLATE clause to set the collation explicitly. 916CONTEXT: PL/pgSQL function mylt2(text,text) line 6 at RETURN 917SELECT mylt2('a', 'B' collate "POSIX") as f; 918 f 919--- 920 f 921(1 row) 922 923-- polymorphism 924SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; 925 unnest 926-------- 927 abc 928 ABC 929 äbc 930 bbc 931(4 rows) 932 933SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; 934 unnest 935-------- 936 abc 937 ABC 938 bbc 939 äbc 940(4 rows) 941 942SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test3)) ORDER BY 1; 943 unnest 944-------- 945 ABC 946 abc 947 bbc 948 äbc 949(4 rows) 950 951CREATE FUNCTION dup (anyelement) RETURNS anyelement 952 AS 'select $1' LANGUAGE sql; 953SELECT a, dup(b) FROM collate_test1 ORDER BY 2; 954 a | dup 955---+----- 956 1 | abc 957 4 | ABC 958 2 | äbc 959 3 | bbc 960(4 rows) 961 962SELECT a, dup(b) FROM collate_test2 ORDER BY 2; 963 a | dup 964---+----- 965 1 | abc 966 4 | ABC 967 3 | bbc 968 2 | äbc 969(4 rows) 970 971SELECT a, dup(b) FROM collate_test3 ORDER BY 2; 972 a | dup 973---+----- 974 4 | ABC 975 1 | abc 976 3 | bbc 977 2 | äbc 978(4 rows) 979 980-- indexes 981CREATE INDEX collate_test1_idx1 ON collate_test1 (b); 982CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "C"); 983CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "C")); -- this is different grammatically 984CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); 985CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "C"); -- fail 986ERROR: collations are not supported by type integer 987CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C")); -- fail 988ERROR: collations are not supported by type integer 989LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "C... 990 ^ 991SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; 992 relname | pg_get_indexdef 993--------------------+------------------------------------------------------------------------------------------------------------------- 994 collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_tests.collate_test1 USING btree (b) 995 collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_tests.collate_test1 USING btree (b COLLATE "C") 996 collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_tests.collate_test1 USING btree (b COLLATE "C") 997 collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX") 998(4 rows) 999 1000-- schema manipulation commands 1001CREATE ROLE regress_test_role; 1002CREATE SCHEMA test_schema; 1003-- We need to do this this way to cope with varying names for encodings: 1004do $$ 1005BEGIN 1006 EXECUTE 'CREATE COLLATION test0 (locale = ' || 1007 quote_literal(current_setting('lc_collate')) || ');'; 1008END 1009$$; 1010CREATE COLLATION test0 FROM "C"; -- fail, duplicate name 1011ERROR: collation "test0" already exists 1012CREATE COLLATION IF NOT EXISTS test0 FROM "C"; -- ok, skipped 1013NOTICE: collation "test0" already exists, skipping 1014CREATE COLLATION IF NOT EXISTS test0 (locale = 'foo'); -- ok, skipped 1015NOTICE: collation "test0" for encoding "UTF8" already exists, skipping 1016do $$ 1017BEGIN 1018 EXECUTE 'CREATE COLLATION test1 (lc_collate = ' || 1019 quote_literal(current_setting('lc_collate')) || 1020 ', lc_ctype = ' || 1021 quote_literal(current_setting('lc_ctype')) || ');'; 1022END 1023$$; 1024CREATE COLLATION test3 (lc_collate = 'en_US.utf8'); -- fail, need lc_ctype 1025ERROR: parameter "lc_ctype" must be specified 1026CREATE COLLATION testx (locale = 'nonsense'); -- fail 1027ERROR: could not create locale "nonsense": No such file or directory 1028DETAIL: The operating system could not find any locale data for the locale name "nonsense". 1029CREATE COLLATION testy (locale = 'en_US.utf8', version = 'foo'); -- fail, no versions for libc 1030ERROR: collation "testy" has no actual version, but a version was specified 1031CREATE COLLATION test4 FROM nonsense; 1032ERROR: collation "nonsense" for encoding "UTF8" does not exist 1033CREATE COLLATION test5 FROM test0; 1034SELECT collname FROM pg_collation WHERE collname LIKE 'test%' ORDER BY 1; 1035 collname 1036---------- 1037 test0 1038 test1 1039 test5 1040(3 rows) 1041 1042ALTER COLLATION test1 RENAME TO test11; 1043ALTER COLLATION test0 RENAME TO test11; -- fail 1044ERROR: collation "test11" for encoding "UTF8" already exists in schema "collate_tests" 1045ALTER COLLATION test1 RENAME TO test22; -- fail 1046ERROR: collation "test1" for encoding "UTF8" does not exist 1047ALTER COLLATION test11 OWNER TO regress_test_role; 1048ALTER COLLATION test11 OWNER TO nonsense; 1049ERROR: role "nonsense" does not exist 1050ALTER COLLATION test11 SET SCHEMA test_schema; 1051COMMENT ON COLLATION test0 IS 'US English'; 1052SELECT collname, nspname, obj_description(pg_collation.oid, 'pg_collation') 1053 FROM pg_collation JOIN pg_namespace ON (collnamespace = pg_namespace.oid) 1054 WHERE collname LIKE 'test%' 1055 ORDER BY 1; 1056 collname | nspname | obj_description 1057----------+---------------+----------------- 1058 test0 | collate_tests | US English 1059 test11 | test_schema | 1060 test5 | collate_tests | 1061(3 rows) 1062 1063DROP COLLATION test0, test_schema.test11, test5; 1064DROP COLLATION test0; -- fail 1065ERROR: collation "test0" for encoding "UTF8" does not exist 1066DROP COLLATION IF EXISTS test0; 1067NOTICE: collation "test0" does not exist, skipping 1068SELECT collname FROM pg_collation WHERE collname LIKE 'test%'; 1069 collname 1070---------- 1071(0 rows) 1072 1073DROP SCHEMA test_schema; 1074DROP ROLE regress_test_role; 1075-- ALTER 1076ALTER COLLATION "en_US" REFRESH VERSION; 1077NOTICE: version has not changed 1078-- dependencies 1079CREATE COLLATION test0 FROM "C"; 1080CREATE TABLE collate_dep_test1 (a int, b text COLLATE test0); 1081CREATE DOMAIN collate_dep_dom1 AS text COLLATE test0; 1082CREATE TYPE collate_dep_test2 AS (x int, y text COLLATE test0); 1083CREATE VIEW collate_dep_test3 AS SELECT text 'foo' COLLATE test0 AS foo; 1084CREATE TABLE collate_dep_test4t (a int, b text); 1085CREATE INDEX collate_dep_test4i ON collate_dep_test4t (b COLLATE test0); 1086DROP COLLATION test0 RESTRICT; -- fail 1087ERROR: cannot drop collation test0 because other objects depend on it 1088DETAIL: table collate_dep_test1 column b depends on collation test0 1089type collate_dep_dom1 depends on collation test0 1090composite type collate_dep_test2 column y depends on collation test0 1091view collate_dep_test3 depends on collation test0 1092index collate_dep_test4i depends on collation test0 1093HINT: Use DROP ... CASCADE to drop the dependent objects too. 1094DROP COLLATION test0 CASCADE; 1095NOTICE: drop cascades to 5 other objects 1096DETAIL: drop cascades to table collate_dep_test1 column b 1097drop cascades to type collate_dep_dom1 1098drop cascades to composite type collate_dep_test2 column y 1099drop cascades to view collate_dep_test3 1100drop cascades to index collate_dep_test4i 1101\d collate_dep_test1 1102 Table "collate_tests.collate_dep_test1" 1103 Column | Type | Collation | Nullable | Default 1104--------+---------+-----------+----------+--------- 1105 a | integer | | | 1106 1107\d collate_dep_test2 1108 Composite type "collate_tests.collate_dep_test2" 1109 Column | Type | Collation | Nullable | Default 1110--------+---------+-----------+----------+--------- 1111 x | integer | | | 1112 1113DROP TABLE collate_dep_test1, collate_dep_test4t; 1114DROP TYPE collate_dep_test2; 1115-- test range types and collations 1116create type textrange_c as range(subtype=text, collation="C"); 1117create type textrange_en_us as range(subtype=text, collation="en_US"); 1118select textrange_c('A','Z') @> 'b'::text; 1119 ?column? 1120---------- 1121 f 1122(1 row) 1123 1124select textrange_en_us('A','Z') @> 'b'::text; 1125 ?column? 1126---------- 1127 t 1128(1 row) 1129 1130drop type textrange_c; 1131drop type textrange_en_us; 1132-- cleanup 1133DROP SCHEMA collate_tests CASCADE; 1134NOTICE: drop cascades to 18 other objects 1135DETAIL: drop cascades to table collate_test1 1136drop cascades to table collate_test_like 1137drop cascades to table collate_test2 1138drop cascades to table collate_test3 1139drop cascades to type testdomain_sv 1140drop cascades to table collate_test4 1141drop cascades to table collate_test5 1142drop cascades to table collate_test10 1143drop cascades to table collate_test6 1144drop cascades to view collview1 1145drop cascades to view collview2 1146drop cascades to view collview3 1147drop cascades to type testdomain 1148drop cascades to function mylt(text,text) 1149drop cascades to function mylt_noninline(text,text) 1150drop cascades to function mylt_plpgsql(text,text) 1151drop cascades to function mylt2(text,text) 1152drop cascades to function dup(anyelement) 1153