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