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