1/* 2 * This test is intended to pass on all platforms supported by Postgres. 3 * We can therefore only assume that the default, C, and POSIX collations 4 * are available --- and since the regression tests are often run in a 5 * C-locale database, these may well all have the same behavior. But 6 * fortunately, the system doesn't know that and will treat them as 7 * incompatible collations. It is therefore at least possible to test 8 * parser behaviors such as collation conflict resolution. This test will, 9 * however, be more revealing when run in a database with non-C locale, 10 * since any departure from C sorting behavior will show as a failure. 11 */ 12CREATE SCHEMA collate_tests; 13SET search_path = collate_tests; 14CREATE TABLE collate_test1 ( 15 a int, 16 b text COLLATE "C" NOT NULL 17); 18\d collate_test1 19 Table "collate_tests.collate_test1" 20 Column | Type | Collation | Nullable | Default 21--------+---------+-----------+----------+--------- 22 a | integer | | | 23 b | text | C | not null | 24 25CREATE TABLE collate_test_fail ( 26 a int COLLATE "C", 27 b text 28); 29ERROR: collations are not supported by type integer 30LINE 2: a int COLLATE "C", 31 ^ 32CREATE TABLE collate_test_like ( 33 LIKE collate_test1 34); 35\d collate_test_like 36 Table "collate_tests.collate_test_like" 37 Column | Type | Collation | Nullable | Default 38--------+---------+-----------+----------+--------- 39 a | integer | | | 40 b | text | C | not null | 41 42CREATE TABLE collate_test2 ( 43 a int, 44 b text COLLATE "POSIX" 45); 46INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'Abc'), (3, 'bbc'), (4, 'ABD'); 47INSERT INTO collate_test2 SELECT * FROM collate_test1; 48SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc'; 49 a | b 50---+----- 51 1 | abc 52 3 | bbc 53(2 rows) 54 55SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C"; 56 a | b 57---+----- 58 1 | abc 59 3 | bbc 60(2 rows) 61 62SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C"; 63 a | b 64---+----- 65 1 | abc 66 3 | bbc 67(2 rows) 68 69SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail 70ERROR: collation mismatch between explicit collations "C" and "POSIX" 71LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "P... 72 ^ 73CREATE DOMAIN testdomain_p AS text COLLATE "POSIX"; 74CREATE DOMAIN testdomain_i AS int COLLATE "POSIX"; -- fail 75ERROR: collations are not supported by type integer 76CREATE TABLE collate_test4 ( 77 a int, 78 b testdomain_p 79); 80INSERT INTO collate_test4 SELECT * FROM collate_test1; 81SELECT a, b FROM collate_test4 ORDER BY b; 82 a | b 83---+----- 84 4 | ABD 85 2 | Abc 86 1 | abc 87 3 | bbc 88(4 rows) 89 90CREATE TABLE collate_test5 ( 91 a int, 92 b testdomain_p COLLATE "C" 93); 94INSERT INTO collate_test5 SELECT * FROM collate_test1; 95SELECT a, b FROM collate_test5 ORDER BY b; 96 a | b 97---+----- 98 4 | ABD 99 2 | Abc 100 1 | abc 101 3 | bbc 102(4 rows) 103 104SELECT a, b FROM collate_test1 ORDER BY b; 105 a | b 106---+----- 107 4 | ABD 108 2 | Abc 109 1 | abc 110 3 | bbc 111(4 rows) 112 113SELECT a, b FROM collate_test2 ORDER BY b; 114 a | b 115---+----- 116 4 | ABD 117 2 | Abc 118 1 | abc 119 3 | bbc 120(4 rows) 121 122SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 123 a | b 124---+----- 125 4 | ABD 126 2 | Abc 127 1 | abc 128 3 | bbc 129(4 rows) 130 131-- star expansion 132SELECT * FROM collate_test1 ORDER BY b; 133 a | b 134---+----- 135 4 | ABD 136 2 | Abc 137 1 | abc 138 3 | bbc 139(4 rows) 140 141SELECT * FROM collate_test2 ORDER BY b; 142 a | b 143---+----- 144 4 | ABD 145 2 | Abc 146 1 | abc 147 3 | bbc 148(4 rows) 149 150-- constant expression folding 151SELECT 'bbc' COLLATE "C" > 'Abc' COLLATE "C" AS "true"; 152 true 153------ 154 t 155(1 row) 156 157SELECT 'bbc' COLLATE "POSIX" < 'Abc' COLLATE "POSIX" AS "false"; 158 false 159------- 160 f 161(1 row) 162 163-- upper/lower 164CREATE TABLE collate_test10 ( 165 a int, 166 x text COLLATE "C", 167 y text COLLATE "POSIX" 168); 169INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ'); 170SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10; 171 a | lower | lower | upper | upper | initcap | initcap 172---+-------+-------+-------+-------+---------+--------- 173 1 | hij | hij | HIJ | HIJ | Hij | Hij 174 2 | hij | hij | HIJ | HIJ | Hij | Hij 175(2 rows) 176 177SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10; 178 a | lower | lower 179---+-------+------- 180 1 | hij | hij 181 2 | hij | hij 182(2 rows) 183 184SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a; 185 a | x | y 186---+-----+----- 187 1 | hij | hij 188 2 | HIJ | HIJ 189(2 rows) 190 191-- backwards parsing 192CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc'; 193CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C"; 194CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10; 195SELECT table_name, view_definition FROM information_schema.views 196 WHERE table_name LIKE 'collview%' ORDER BY 1; 197 table_name | view_definition 198------------+------------------------------------------------------------------------------ 199 collview1 | SELECT collate_test1.a, + 200 | collate_test1.b + 201 | FROM collate_test1 + 202 | WHERE ((collate_test1.b COLLATE "C") >= 'bbc'::text); 203 collview2 | SELECT collate_test1.a, + 204 | collate_test1.b + 205 | FROM collate_test1 + 206 | ORDER BY (collate_test1.b COLLATE "C"); 207 collview3 | SELECT collate_test10.a, + 208 | lower(((collate_test10.x || collate_test10.x) COLLATE "POSIX")) AS lower+ 209 | FROM collate_test10; 210(3 rows) 211 212-- collation propagation in various expression types 213SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2; 214 a | coalesce 215---+---------- 216 4 | ABD 217 2 | Abc 218 1 | abc 219 3 | bbc 220(4 rows) 221 222SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2; 223 a | coalesce 224---+---------- 225 4 | ABD 226 2 | Abc 227 1 | abc 228 3 | bbc 229(4 rows) 230 231SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10; 232 a | lower | lower 233---+-------+------- 234 1 | hij | hij 235 2 | hij | hij 236(2 rows) 237 238SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3; 239 a | b | greatest 240---+-----+---------- 241 2 | Abc | CCC 242 4 | ABD | CCC 243 1 | abc | abc 244 3 | bbc | bbc 245(4 rows) 246 247SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3; 248 a | b | greatest 249---+-----+---------- 250 2 | Abc | CCC 251 4 | ABD | CCC 252 1 | abc | abc 253 3 | bbc | bbc 254(4 rows) 255 256SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10; 257 a | x | y | lower | lower 258---+-----+-----+-------+------- 259 1 | hij | hij | hij | hij 260 2 | HIJ | HIJ | foo | foo 261(2 rows) 262 263SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2; 264 a | nullif 265---+-------- 266 4 | ABD 267 2 | Abc 268 3 | bbc 269 1 | 270(4 rows) 271 272SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2; 273 a | nullif 274---+-------- 275 4 | ABD 276 2 | Abc 277 3 | bbc 278 1 | 279(4 rows) 280 281SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10; 282 a | lower | lower 283---+-------+------- 284 1 | hij | hij 285 2 | hij | hij 286(2 rows) 287 288SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2; 289 a | b 290---+------ 291 4 | ABD 292 2 | Abc 293 1 | abcd 294 3 | bbc 295(4 rows) 296 297SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2; 298 a | b 299---+------ 300 4 | ABD 301 2 | Abc 302 1 | abcd 303 3 | bbc 304(4 rows) 305 306CREATE DOMAIN testdomain AS text; 307SELECT a, b::testdomain FROM collate_test1 ORDER BY 2; 308 a | b 309---+----- 310 4 | ABD 311 2 | Abc 312 1 | abc 313 3 | bbc 314(4 rows) 315 316SELECT a, b::testdomain FROM collate_test2 ORDER BY 2; 317 a | b 318---+----- 319 4 | ABD 320 2 | Abc 321 1 | abc 322 3 | bbc 323(4 rows) 324 325SELECT a, b::testdomain_p FROM collate_test2 ORDER BY 2; 326 a | b 327---+----- 328 4 | ABD 329 2 | Abc 330 1 | abc 331 3 | bbc 332(4 rows) 333 334SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10; 335 a | lower | lower 336---+-------+------- 337 1 | hij | hij 338 2 | hij | hij 339(2 rows) 340 341SELECT min(b), max(b) FROM collate_test1; 342 min | max 343-----+----- 344 ABD | bbc 345(1 row) 346 347SELECT min(b), max(b) FROM collate_test2; 348 min | max 349-----+----- 350 ABD | bbc 351(1 row) 352 353SELECT array_agg(b ORDER BY b) FROM collate_test1; 354 array_agg 355------------------- 356 {ABD,Abc,abc,bbc} 357(1 row) 358 359SELECT array_agg(b ORDER BY b) FROM collate_test2; 360 array_agg 361------------------- 362 {ABD,Abc,abc,bbc} 363(1 row) 364 365-- In aggregates, ORDER BY expressions don't affect aggregate's collation 366SELECT string_agg(x COLLATE "C", y COLLATE "POSIX") FROM collate_test10; -- fail 367ERROR: collation mismatch between explicit collations "C" and "POSIX" 368LINE 1: SELECT string_agg(x COLLATE "C", y COLLATE "POSIX") FROM col... 369 ^ 370SELECT array_agg(x COLLATE "C" ORDER BY y COLLATE "POSIX") FROM collate_test10; 371 array_agg 372----------- 373 {HIJ,hij} 374(1 row) 375 376SELECT array_agg(a ORDER BY x COLLATE "C", y COLLATE "POSIX") FROM collate_test10; 377 array_agg 378----------- 379 {2,1} 380(1 row) 381 382SELECT array_agg(a ORDER BY x||y) FROM collate_test10; -- fail 383ERROR: collation mismatch between implicit collations "C" and "POSIX" 384LINE 1: SELECT array_agg(a ORDER BY x||y) FROM collate_test10; 385 ^ 386HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 387SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2; 388 a | b 389---+----- 390 4 | ABD 391 4 | ABD 392 2 | Abc 393 2 | Abc 394 1 | abc 395 1 | abc 396 3 | bbc 397 3 | bbc 398(8 rows) 399 400SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2; 401 a | b 402---+----- 403 4 | ABD 404 2 | Abc 405 1 | abc 406 3 | bbc 407(4 rows) 408 409SELECT a, b FROM collate_test2 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test2 WHERE a > 1 ORDER BY 2; 410 a | b 411---+----- 412 2 | Abc 413 3 | bbc 414(2 rows) 415 416SELECT a, b FROM collate_test2 EXCEPT SELECT a, b FROM collate_test2 WHERE a < 2 ORDER BY 2; 417 a | b 418---+----- 419 4 | ABD 420 2 | Abc 421 3 | bbc 422(3 rows) 423 424SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 425ERROR: could not determine which collation to use for string comparison 426HINT: Use the COLLATE clause to set the collation explicitly. 427SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- ok 428 a | b 429---+----- 430 1 | abc 431 2 | Abc 432 3 | bbc 433 4 | ABD 434 1 | abc 435 2 | Abc 436 3 | bbc 437 4 | ABD 438(8 rows) 439 440SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 441ERROR: collation mismatch between implicit collations "C" and "POSIX" 442LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat... 443 ^ 444HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 445SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- ok 446 a | b 447---+----- 448 4 | ABD 449 2 | Abc 450 1 | abc 451 3 | bbc 452(4 rows) 453 454SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 455ERROR: collation mismatch between implicit collations "C" and "POSIX" 456LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col... 457 ^ 458HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 459SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail 460ERROR: collation mismatch between implicit collations "C" and "POSIX" 461LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla... 462 ^ 463HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 464CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- fail 465ERROR: no collation was derived for column "b" with collatable type text 466HINT: Use the COLLATE clause to set the collation explicitly. 467-- ideally this would be a parse-time error, but for now it must be run-time: 468select x < y from collate_test10; -- fail 469ERROR: could not determine which collation to use for string comparison 470HINT: Use the COLLATE clause to set the collation explicitly. 471select x || y from collate_test10; -- ok, because || is not collation aware 472 ?column? 473---------- 474 hijhij 475 HIJHIJ 476(2 rows) 477 478select x, y from collate_test10 order by x || y; -- not so ok 479ERROR: collation mismatch between implicit collations "C" and "POSIX" 480LINE 1: select x, y from collate_test10 order by x || y; 481 ^ 482HINT: You can choose the collation by applying the COLLATE clause to one or both expressions. 483-- collation mismatch between recursive and non-recursive term 484WITH RECURSIVE foo(x) AS 485 (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x) 486 UNION ALL 487 SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10) 488SELECT * FROM foo; 489ERROR: recursive query "foo" column 1 has collation "C" in non-recursive term but collation "POSIX" overall 490LINE 2: (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x) 491 ^ 492HINT: Use the COLLATE clause to set the collation of the non-recursive term. 493SELECT a, b, a < b as lt FROM 494 (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b); 495 a | b | lt 496---+---+---- 497 a | B | f 498 A | b | t 499(2 rows) 500 501-- casting 502SELECT CAST('42' AS text COLLATE "C"); 503ERROR: syntax error at or near "COLLATE" 504LINE 1: SELECT CAST('42' AS text COLLATE "C"); 505 ^ 506SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2; 507 a | b 508---+----- 509 4 | ABD 510 2 | Abc 511 1 | abc 512 3 | bbc 513(4 rows) 514 515SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2; 516 a | b 517---+----- 518 4 | ABD 519 2 | Abc 520 1 | abc 521 3 | bbc 522(4 rows) 523 524-- polymorphism 525SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1; 526 unnest 527-------- 528 ABD 529 Abc 530 abc 531 bbc 532(4 rows) 533 534SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1; 535 unnest 536-------- 537 ABD 538 Abc 539 abc 540 bbc 541(4 rows) 542 543CREATE FUNCTION dup (anyelement) RETURNS anyelement 544 AS 'select $1' LANGUAGE sql; 545SELECT a, dup(b) FROM collate_test1 ORDER BY 2; 546 a | dup 547---+----- 548 4 | ABD 549 2 | Abc 550 1 | abc 551 3 | bbc 552(4 rows) 553 554SELECT a, dup(b) FROM collate_test2 ORDER BY 2; 555 a | dup 556---+----- 557 4 | ABD 558 2 | Abc 559 1 | abc 560 3 | bbc 561(4 rows) 562 563-- indexes 564CREATE INDEX collate_test1_idx1 ON collate_test1 (b); 565CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "POSIX"); 566CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "POSIX")); -- this is different grammatically 567CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX")); 568CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "POSIX"); -- fail 569ERROR: collations are not supported by type integer 570CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail 571ERROR: collations are not supported by type integer 572LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "P... 573 ^ 574SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1; 575 relname | pg_get_indexdef 576--------------------+------------------------------------------------------------------------------------------------------------------- 577 collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_tests.collate_test1 USING btree (b) 578 collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_tests.collate_test1 USING btree (b COLLATE "POSIX") 579 collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_tests.collate_test1 USING btree (b COLLATE "POSIX") 580 collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX") 581(4 rows) 582 583-- foreign keys 584-- force indexes and mergejoins to be used for FK checking queries, 585-- else they might not exercise collation-dependent operators 586SET enable_seqscan TO 0; 587SET enable_hashjoin TO 0; 588SET enable_nestloop TO 0; 589CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY); 590INSERT INTO collate_test20 VALUES ('foo'), ('bar'); 591CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20); 592INSERT INTO collate_test21 VALUES ('foo'), ('bar'); 593INSERT INTO collate_test21 VALUES ('baz'); -- fail 594ERROR: insert or update on table "collate_test21" violates foreign key constraint "collate_test21_f2_fkey" 595DETAIL: Key (f2)=(baz) is not present in table "collate_test20". 596CREATE TABLE collate_test22 (f2 text COLLATE "POSIX"); 597INSERT INTO collate_test22 VALUES ('foo'), ('bar'), ('baz'); 598ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; -- fail 599ERROR: insert or update on table "collate_test22" violates foreign key constraint "collate_test22_f2_fkey" 600DETAIL: Key (f2)=(baz) is not present in table "collate_test20". 601DELETE FROM collate_test22 WHERE f2 = 'baz'; 602ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; 603RESET enable_seqscan; 604RESET enable_hashjoin; 605RESET enable_nestloop; 606-- EXPLAIN 607EXPLAIN (COSTS OFF) 608 SELECT * FROM collate_test10 ORDER BY x, y; 609 QUERY PLAN 610---------------------------------------------- 611 Sort 612 Sort Key: x COLLATE "C", y COLLATE "POSIX" 613 -> Seq Scan on collate_test10 614(3 rows) 615 616EXPLAIN (COSTS OFF) 617 SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST; 618 QUERY PLAN 619----------------------------------------------------------- 620 Sort 621 Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST 622 -> Seq Scan on collate_test10 623(3 rows) 624 625-- CREATE/DROP COLLATION 626CREATE COLLATION mycoll1 FROM "C"; 627CREATE COLLATION mycoll2 ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" ); 628CREATE COLLATION mycoll3 FROM "default"; -- intentionally unsupported 629ERROR: collation "default" cannot be copied 630DROP COLLATION mycoll1; 631CREATE TABLE collate_test23 (f1 text collate mycoll2); 632DROP COLLATION mycoll2; -- fail 633ERROR: cannot drop collation mycoll2 because other objects depend on it 634DETAIL: table collate_test23 column f1 depends on collation mycoll2 635HINT: Use DROP ... CASCADE to drop the dependent objects too. 636-- 9.1 bug with useless COLLATE in an expression subject to length coercion 637CREATE TEMP TABLE vctable (f1 varchar(25)); 638INSERT INTO vctable VALUES ('foo' COLLATE "C"); 639SELECT collation for ('foo'); -- unknown type - null 640 pg_collation_for 641------------------ 642 643(1 row) 644 645SELECT collation for ('foo'::text); 646 pg_collation_for 647------------------ 648 "default" 649(1 row) 650 651SELECT collation for ((SELECT a FROM collate_test1 LIMIT 1)); -- non-collatable type - error 652ERROR: collations are not supported by type integer 653SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1)); 654 pg_collation_for 655------------------ 656 "C" 657(1 row) 658 659-- old bug with not dropping COLLATE when coercing to non-collatable type 660CREATE VIEW collate_on_int AS 661SELECT c1+1 AS c1p FROM 662 (SELECT ('4' COLLATE "C")::INT AS c1) ss; 663\d+ collate_on_int 664 View "collate_tests.collate_on_int" 665 Column | Type | Collation | Nullable | Default | Storage | Description 666--------+---------+-----------+----------+---------+---------+------------- 667 c1p | integer | | | | plain | 668View definition: 669 SELECT ss.c1 + 1 AS c1p 670 FROM ( SELECT 4 AS c1) ss; 671 672-- 673-- Clean up. Many of these table names will be re-used if the user is 674-- trying to run any platform-specific collation tests later, so we 675-- must get rid of them. 676-- 677\set VERBOSITY terse 678DROP SCHEMA collate_tests CASCADE; 679NOTICE: drop cascades to 18 other objects 680