src/test/regress/expected/collate.out - master - githubmirror/postgres

src/test/regress/expected/collate.out
820 строк26 KB
Tom Lane
Fix LIKE/regex optimization for indexscan with exact-match pattern.
06 июл 2026, 20:06
06 июл 2026, 20:062d7808e
/*
 * This test is intended to pass on all platforms supported by Postgres.
 * We can therefore only assume that the default, C, and POSIX collations
 * are available --- and since the regression tests are often run in a
 * C-locale database, these may well all have the same behavior.  But
 * fortunately, the system doesn't know that and will treat them as
 * incompatible collations.  It is therefore at least possible to test
 * parser behaviors such as collation conflict resolution.  This test will,
 * however, be more revealing when run in a database with non-C locale,
 * since any departure from C sorting behavior will show as a failure.
 */
CREATE SCHEMA collate_tests;
SET search_path = collate_tests;
CREATE TABLE collate_test1 (
    a int,
    b text COLLATE "C" NOT NULL
);
\d collate_test1
        Table "collate_tests.collate_test1"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    | C         | not null | 

CREATE TABLE collate_test_fail (
    a int COLLATE "C",
    b text
);
ERROR:  collations are not supported by type integer
LINE 2:     a int COLLATE "C",
                  ^
CREATE TABLE collate_test_like (
    LIKE collate_test1
);
\d collate_test_like
      Table "collate_tests.collate_test_like"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 a      | integer |           |          | 
 b      | text    | C         | not null | 

CREATE TABLE collate_test2 (
    a int,
    b text COLLATE "POSIX"
);
INSERT INTO collate_test1 VALUES (1, 'abc'), (2, 'Abc'), (3, 'bbc'), (4, 'ABD');
INSERT INTO collate_test2 SELECT * FROM collate_test1;
SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc';
 a |  b  
---+-----
 1 | abc
 3 | bbc
(2 rows)

SELECT * FROM collate_test1 WHERE b >= 'abc' COLLATE "C";
 a |  b  
---+-----
 1 | abc
 3 | bbc
(2 rows)

SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'abc' COLLATE "C";
 a |  b  
---+-----
 1 | abc
 3 | bbc
(2 rows)

SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "POSIX"; -- fail
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: ...* FROM collate_test1 WHERE b COLLATE "C" >= 'bbc' COLLATE "P...
                                                             ^
CREATE DOMAIN testdomain_p AS text COLLATE "POSIX";
CREATE DOMAIN testdomain_i AS int COLLATE "POSIX"; -- fail
ERROR:  collations are not supported by type integer
LINE 1: CREATE DOMAIN testdomain_i AS int COLLATE "POSIX";
                                      ^
CREATE TABLE collate_test4 (
    a int,
    b testdomain_p
);
INSERT INTO collate_test4 SELECT * FROM collate_test1;
SELECT a, b FROM collate_test4 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

CREATE TABLE collate_test5 (
    a int,
    b testdomain_p COLLATE "C"
);
INSERT INTO collate_test5 SELECT * FROM collate_test1;
SELECT a, b FROM collate_test5 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b FROM collate_test1 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b FROM collate_test2 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

-- star expansion
SELECT * FROM collate_test1 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT * FROM collate_test2 ORDER BY b;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

-- constant expression folding
SELECT 'bbc' COLLATE "C" > 'Abc' COLLATE "C" AS "true";
 true 
------
 t
(1 row)

SELECT 'bbc' COLLATE "POSIX" < 'Abc' COLLATE "POSIX" AS "false";
 false 
-------
 f
(1 row)

-- upper/lower
CREATE TABLE collate_test10 (
    a int,
    x text COLLATE "C",
    y text COLLATE "POSIX"
);
INSERT INTO collate_test10 VALUES (1, 'hij', 'hij'), (2, 'HIJ', 'HIJ');
SELECT a, lower(x), lower(y), upper(x), upper(y), initcap(x), initcap(y) FROM collate_test10;
 a | lower | lower | upper | upper | initcap | initcap 
---+-------+-------+-------+-------+---------+---------
 1 | hij   | hij   | HIJ   | HIJ   | Hij     | Hij
 2 | hij   | hij   | HIJ   | HIJ   | Hij     | Hij
(2 rows)

SELECT a, lower(x COLLATE "C"), lower(y COLLATE "C") FROM collate_test10;
 a | lower | lower 
---+-------+-------
 1 | hij   | hij
 2 | hij   | hij
(2 rows)

SELECT a, x, y FROM collate_test10 ORDER BY lower(y), a;
 a |  x  |  y  
---+-----+-----
 1 | hij | hij
 2 | HIJ | HIJ
(2 rows)

-- backwards parsing
CREATE VIEW collview1 AS SELECT * FROM collate_test1 WHERE b COLLATE "C" >= 'bbc';
CREATE VIEW collview2 AS SELECT a, b FROM collate_test1 ORDER BY b COLLATE "C";
CREATE VIEW collview3 AS SELECT a, lower((x || x) COLLATE "POSIX") FROM collate_test10;
SELECT table_name, view_definition FROM information_schema.views
  WHERE table_name LIKE 'collview%' ORDER BY 1;
 table_name |                view_definition                 
------------+------------------------------------------------
 collview1  |  SELECT a,                                    +
            |     b                                         +
            |    FROM collate_test1                         +
            |   WHERE ((b COLLATE "C") >= 'bbc'::text);
 collview2  |  SELECT a,                                    +
            |     b                                         +
            |    FROM collate_test1                         +
            |   ORDER BY (b COLLATE "C");
 collview3  |  SELECT a,                                    +
            |     lower(((x || x) COLLATE "POSIX")) AS lower+
            |    FROM collate_test10;
(3 rows)

-- collation propagation in various expression types
SELECT a, coalesce(b, 'foo') FROM collate_test1 ORDER BY 2;
 a | coalesce 
---+----------
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, coalesce(b, 'foo') FROM collate_test2 ORDER BY 2;
 a | coalesce 
---+----------
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, lower(coalesce(x, 'foo')), lower(coalesce(y, 'foo')) FROM collate_test10;
 a | lower | lower 
---+-------+-------
 1 | hij   | hij
 2 | hij   | hij
(2 rows)

SELECT a, b, greatest(b, 'CCC') FROM collate_test1 ORDER BY 3;
 a |  b  | greatest 
---+-----+----------
 2 | Abc | CCC
 4 | ABD | CCC
 1 | abc | abc
 3 | bbc | bbc
(4 rows)

SELECT a, b, greatest(b, 'CCC') FROM collate_test2 ORDER BY 3;
 a |  b  | greatest 
---+-----+----------
 2 | Abc | CCC
 4 | ABD | CCC
 1 | abc | abc
 3 | bbc | bbc
(4 rows)

SELECT a, x, y, lower(greatest(x, 'foo')), lower(greatest(y, 'foo')) FROM collate_test10;
 a |  x  |  y  | lower | lower 
---+-----+-----+-------+-------
 1 | hij | hij | hij   | hij
 2 | HIJ | HIJ | foo   | foo
(2 rows)

SELECT a, nullif(b, 'abc') FROM collate_test1 ORDER BY 2;
 a | nullif 
---+--------
 4 | ABD
 2 | Abc
 3 | bbc
 1 | 
(4 rows)

SELECT a, nullif(b, 'abc') FROM collate_test2 ORDER BY 2;
 a | nullif 
---+--------
 4 | ABD
 2 | Abc
 3 | bbc
 1 | 
(4 rows)

SELECT a, lower(nullif(x, 'foo')), lower(nullif(y, 'foo')) FROM collate_test10;
 a | lower | lower 
---+-------+-------
 1 | hij   | hij
 2 | hij   | hij
(2 rows)

SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test1 ORDER BY 2;
 a |  b   
---+------
 4 | ABD
 2 | Abc
 1 | abcd
 3 | bbc
(4 rows)

SELECT a, CASE b WHEN 'abc' THEN 'abcd' ELSE b END FROM collate_test2 ORDER BY 2;
 a |  b   
---+------
 4 | ABD
 2 | Abc
 1 | abcd
 3 | bbc
(4 rows)

CREATE DOMAIN testdomain AS text;
SELECT a, b::testdomain FROM collate_test1 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b::testdomain FROM collate_test2 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b::testdomain_p FROM collate_test2 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, lower(x::testdomain), lower(y::testdomain) FROM collate_test10;
 a | lower | lower 
---+-------+-------
 1 | hij   | hij
 2 | hij   | hij
(2 rows)

SELECT min(b), max(b) FROM collate_test1;
 min | max 
-----+-----
 ABD | bbc
(1 row)

SELECT min(b), max(b) FROM collate_test2;
 min | max 
-----+-----
 ABD | bbc
(1 row)

SELECT array_agg(b ORDER BY b) FROM collate_test1;
     array_agg     
-------------------
 {ABD,Abc,abc,bbc}
(1 row)

SELECT array_agg(b ORDER BY b) FROM collate_test2;
     array_agg     
-------------------
 {ABD,Abc,abc,bbc}
(1 row)

-- In aggregates, ORDER BY expressions don't affect aggregate's collation
SELECT string_agg(x COLLATE "C", y COLLATE "POSIX") FROM collate_test10;  -- fail
ERROR:  collation mismatch between explicit collations "C" and "POSIX"
LINE 1: SELECT string_agg(x COLLATE "C", y COLLATE "POSIX") FROM col...
                                           ^
SELECT array_agg(x COLLATE "C" ORDER BY y COLLATE "POSIX") FROM collate_test10;
 array_agg 
-----------
 {HIJ,hij}
(1 row)

SELECT array_agg(a ORDER BY x COLLATE "C", y COLLATE "POSIX") FROM collate_test10;
 array_agg 
-----------
 {2,1}
(1 row)

SELECT array_agg(a ORDER BY x||y) FROM collate_test10;  -- fail
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: SELECT array_agg(a ORDER BY x||y) FROM collate_test10;
                                       ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test1 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 4 | ABD
 2 | Abc
 2 | Abc
 1 | abc
 1 | abc
 3 | bbc
 3 | bbc
(8 rows)

SELECT a, b FROM collate_test2 UNION SELECT a, b FROM collate_test2 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b FROM collate_test2 WHERE a < 4 INTERSECT SELECT a, b FROM collate_test2 WHERE a > 1 ORDER BY 2;
 a |  b  
---+-----
 2 | Abc
 3 | bbc
(2 rows)

SELECT a, b FROM collate_test2 EXCEPT SELECT a, b FROM collate_test2 WHERE a < 2 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 3 | bbc
(3 rows)

SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- ok
 a |  b  
---+-----
 1 | abc
 2 | Abc
 3 | bbc
 4 | ABD
 1 | abc
 2 | Abc
 3 | bbc
 4 | ABD
(8 rows)

SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: SELECT a, b FROM collate_test1 UNION SELECT a, b FROM collat...
                                                       ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
SELECT a, b COLLATE "C" FROM collate_test1 UNION SELECT a, b FROM collate_test2 ORDER BY 2; -- ok
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: ...ELECT a, b FROM collate_test1 INTERSECT SELECT a, b FROM col...
                                                             ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM collate_test2 ORDER BY 2; -- fail
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: SELECT a, b FROM collate_test1 EXCEPT SELECT a, b FROM colla...
                                                        ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
CREATE TABLE test_u AS SELECT a, b FROM collate_test1 UNION ALL SELECT a, b FROM collate_test2; -- fail
ERROR:  no collation was derived for column "b" with collatable type text
HINT:  Use the COLLATE clause to set the collation explicitly.
-- ideally this would be a parse-time error, but for now it must be run-time:
select x < y from collate_test10; -- fail
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.
select x || y from collate_test10; -- ok, because || is not collation aware
 ?column? 
----------
 hijhij
 HIJHIJ
(2 rows)

select x, y from collate_test10 order by x || y; -- not so ok
ERROR:  collation mismatch between implicit collations "C" and "POSIX"
LINE 1: select x, y from collate_test10 order by x || y;
                                                      ^
HINT:  You can choose the collation by applying the COLLATE clause to one or both expressions.
-- collation mismatch between recursive and non-recursive term
WITH RECURSIVE foo(x) AS
   (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
   UNION ALL
   SELECT (x || 'c') COLLATE "POSIX" FROM foo WHERE length(x) < 10)
SELECT * FROM foo;
ERROR:  recursive query "foo" column 1 has collation "C" in non-recursive term but collation "POSIX" overall
LINE 2:    (SELECT x FROM (VALUES('a' COLLATE "C"),('b')) t(x)
                   ^
HINT:  Use the COLLATE clause to set the collation of the non-recursive term.
SELECT a, b, a < b as lt FROM
  (VALUES ('a', 'B'), ('A', 'b' COLLATE "C")) v(a,b);
 a | b | lt 
---+---+----
 a | B | f
 A | b | t
(2 rows)

-- collation mismatch in subselects
SELECT * FROM collate_test10 WHERE (x, y) NOT IN (SELECT y, x FROM collate_test10);
ERROR:  could not determine which collation to use for string hashing
HINT:  Use the COLLATE clause to set the collation explicitly.
-- now it works with overrides
SELECT * FROM collate_test10 WHERE (x COLLATE "POSIX", y COLLATE "C") NOT IN (SELECT y, x FROM collate_test10);
 a | x | y 
---+---+---
(0 rows)

SELECT * FROM collate_test10 WHERE (x, y) NOT IN (SELECT y COLLATE "C", x COLLATE "POSIX" FROM collate_test10);
 a | x | y 
---+---+---
(0 rows)

-- casting
SELECT CAST('42' AS text COLLATE "C");
ERROR:  syntax error at or near "COLLATE"
LINE 1: SELECT CAST('42' AS text COLLATE "C");
                                 ^
SELECT a, CAST(b AS varchar) FROM collate_test1 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, CAST(b AS varchar) FROM collate_test2 ORDER BY 2;
 a |  b  
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

-- result of a SQL function
CREATE FUNCTION vc (text) RETURNS text LANGUAGE sql
    AS 'select $1::varchar';
SELECT a, b FROM collate_test1 ORDER BY a, vc(b);
 a |  b  
---+-----
 1 | abc
 2 | Abc
 3 | bbc
 4 | ABD
(4 rows)

-- polymorphism
SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test1)) ORDER BY 1;
 unnest 
--------
 ABD
 Abc
 abc
 bbc
(4 rows)

SELECT * FROM unnest((SELECT array_agg(b ORDER BY b) FROM collate_test2)) ORDER BY 1;
 unnest 
--------
 ABD
 Abc
 abc
 bbc
(4 rows)

CREATE FUNCTION dup (anyelement) RETURNS anyelement
    AS 'select $1' LANGUAGE sql;
SELECT a, dup(b) FROM collate_test1 ORDER BY 2;
 a | dup 
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

SELECT a, dup(b) FROM collate_test2 ORDER BY 2;
 a | dup 
---+-----
 4 | ABD
 2 | Abc
 1 | abc
 3 | bbc
(4 rows)

-- indexes
CREATE INDEX collate_test1_idx1 ON collate_test1 (b);
CREATE INDEX collate_test1_idx2 ON collate_test1 (b COLLATE "POSIX");
CREATE INDEX collate_test1_idx3 ON collate_test1 ((b COLLATE "POSIX")); -- this is different grammatically
CREATE INDEX collate_test1_idx4 ON collate_test1 (((b||'foo') COLLATE "POSIX"));
CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE "POSIX"); -- fail
ERROR:  collations are not supported by type integer
LINE 1: CREATE INDEX collate_test1_idx5 ON collate_test1 (a COLLATE ...
                                                          ^
CREATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "POSIX")); -- fail
ERROR:  collations are not supported by type integer
LINE 1: ...ATE INDEX collate_test1_idx6 ON collate_test1 ((a COLLATE "P...
                                                             ^
SELECT relname, pg_get_indexdef(oid) FROM pg_class WHERE relname LIKE 'collate_test%_idx%' ORDER BY 1;
      relname       |                                                  pg_get_indexdef                                                  
--------------------+-------------------------------------------------------------------------------------------------------------------
 collate_test1_idx1 | CREATE INDEX collate_test1_idx1 ON collate_tests.collate_test1 USING btree (b)
 collate_test1_idx2 | CREATE INDEX collate_test1_idx2 ON collate_tests.collate_test1 USING btree (b COLLATE "POSIX")
 collate_test1_idx3 | CREATE INDEX collate_test1_idx3 ON collate_tests.collate_test1 USING btree (b COLLATE "POSIX")
 collate_test1_idx4 | CREATE INDEX collate_test1_idx4 ON collate_tests.collate_test1 USING btree (((b || 'foo'::text)) COLLATE "POSIX")
(4 rows)

-- foreign keys
-- force indexes and mergejoins to be used for FK checking queries,
-- else they might not exercise collation-dependent operators
SET enable_seqscan TO 0;
SET enable_hashjoin TO 0;
SET enable_nestloop TO 0;
CREATE TABLE collate_test20 (f1 text COLLATE "C" PRIMARY KEY);
INSERT INTO collate_test20 VALUES ('foo'), ('bar');
CREATE TABLE collate_test21 (f2 text COLLATE "POSIX" REFERENCES collate_test20);
INSERT INTO collate_test21 VALUES ('foo'), ('bar');
INSERT INTO collate_test21 VALUES ('baz'); -- fail
ERROR:  insert or update on table "collate_test21" violates foreign key constraint "collate_test21_f2_fkey"
DETAIL:  Key (f2)=(baz) is not present in table "collate_test20".
CREATE TABLE collate_test22 (f2 text COLLATE "POSIX");
INSERT INTO collate_test22 VALUES ('foo'), ('bar'), ('baz');
ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20; -- fail
ERROR:  insert or update on table "collate_test22" violates foreign key constraint "collate_test22_f2_fkey"
DETAIL:  Key (f2)=(baz) is not present in table "collate_test20".
DELETE FROM collate_test22 WHERE f2 = 'baz';
ALTER TABLE collate_test22 ADD FOREIGN KEY (f2) REFERENCES collate_test20;
RESET enable_seqscan;
RESET enable_hashjoin;
RESET enable_nestloop;
-- EXPLAIN
EXPLAIN (COSTS OFF)
  SELECT * FROM collate_test10 ORDER BY x, y;
                  QUERY PLAN                  
----------------------------------------------
 Sort
   Sort Key: x COLLATE "C", y COLLATE "POSIX"
   ->  Seq Scan on collate_test10
(3 rows)

EXPLAIN (COSTS OFF)
  SELECT * FROM collate_test10 ORDER BY x DESC, y COLLATE "C" ASC NULLS FIRST;
                        QUERY PLAN                         
-----------------------------------------------------------
 Sort
   Sort Key: x COLLATE "C" DESC, y COLLATE "C" NULLS FIRST
   ->  Seq Scan on collate_test10
(3 rows)

-- CREATE/DROP COLLATION
CREATE COLLATION builtin_c ( PROVIDER = builtin, LOCALE = "C" );
SELECT b FROM collate_test1 ORDER BY b COLLATE builtin_c;
  b  
-----
 ABD
 Abc
 abc
 bbc
(4 rows)

CREATE COLLATION builtin2 ( PROVIDER = builtin ); -- fails
ERROR:  parameter "locale" must be specified
CREATE COLLATION builtin2 ( PROVIDER = builtin, LOCALE = "en_US" ); -- fails
ERROR:  invalid locale name "en_US" for builtin provider
CREATE COLLATION builtin2 ( PROVIDER = builtin, LC_CTYPE = "C", LC_COLLATE = "C" ); -- fails
ERROR:  parameter "locale" must be specified
CREATE COLLATION mycoll1 FROM "C";
CREATE COLLATION mycoll2 ( LC_COLLATE = "POSIX", LC_CTYPE = "POSIX" );
CREATE COLLATION mycoll3 FROM "default";  -- intentionally unsupported
ERROR:  collation "default" cannot be copied
DROP COLLATION mycoll1;
CREATE TABLE collate_test23 (f1 text collate mycoll2);
DROP COLLATION mycoll2;  -- fail
ERROR:  cannot drop collation mycoll2 because other objects depend on it
DETAIL:  column f1 of table collate_test23 depends on collation mycoll2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- invalid: non-lowercase quoted identifiers
CREATE COLLATION case_coll ("Lc_Collate" = "POSIX", "Lc_Ctype" = "POSIX");
ERROR:  collation attribute "Lc_Collate" not recognized
LINE 1: CREATE COLLATION case_coll ("Lc_Collate" = "POSIX", "Lc_Ctyp...
                                    ^
-- 9.1 bug with useless COLLATE in an expression subject to length coercion
CREATE TEMP TABLE vctable (f1 varchar(25));
INSERT INTO vctable VALUES ('foo' COLLATE "C");
SELECT collation for ('foo'); -- unknown type - null
 pg_collation_for 
------------------
 
(1 row)

SELECT collation for ('foo'::text);
 pg_collation_for 
------------------
 "default"
(1 row)

SELECT collation for ((SELECT a FROM collate_test1 LIMIT 1)); -- non-collatable type - error
ERROR:  collations are not supported by type integer
SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1));
 pg_collation_for 
------------------
 "C"
(1 row)

-- old bug with not dropping COLLATE when coercing to non-collatable type
CREATE VIEW collate_on_int AS
SELECT c1+1 AS c1p FROM
  (SELECT ('4' COLLATE "C")::INT AS c1) ss;
\d+ collate_on_int
                    View "collate_tests.collate_on_int"
 Column |  Type   | Collation | Nullable | Default | Storage | Description 
--------+---------+-----------+----------+---------+---------+-------------
 c1p    | integer |           |          |         | plain   | 
View definition:
 SELECT c1 + 1 AS c1p
   FROM ( SELECT 4 AS c1) ss;

-- Check conflicting or redundant options in CREATE COLLATION
-- LC_COLLATE
CREATE COLLATION coll_dup_chk (LC_COLLATE = "POSIX", LC_COLLATE = "NONSENSE", LC_CTYPE = "POSIX");
ERROR:  conflicting or redundant options
LINE 1: ...ATE COLLATION coll_dup_chk (LC_COLLATE = "POSIX", LC_COLLATE...
                                                             ^
-- LC_CTYPE
CREATE COLLATION coll_dup_chk (LC_CTYPE = "POSIX", LC_CTYPE = "NONSENSE", LC_COLLATE = "POSIX");
ERROR:  conflicting or redundant options
LINE 1: ...REATE COLLATION coll_dup_chk (LC_CTYPE = "POSIX", LC_CTYPE =...
                                                             ^
-- PROVIDER
CREATE COLLATION coll_dup_chk (PROVIDER = icu, PROVIDER = NONSENSE, LC_COLLATE = "POSIX", LC_CTYPE = "POSIX");
ERROR:  conflicting or redundant options
LINE 1: CREATE COLLATION coll_dup_chk (PROVIDER = icu, PROVIDER = NO...
                                                       ^
-- LOCALE
CREATE COLLATION case_sensitive (LOCALE = '', LOCALE = "NONSENSE");
ERROR:  conflicting or redundant options
LINE 1: CREATE COLLATION case_sensitive (LOCALE = '', LOCALE = "NONS...
                                                      ^
-- DETERMINISTIC
CREATE COLLATION coll_dup_chk (DETERMINISTIC = TRUE, DETERMINISTIC = NONSENSE, LOCALE = '');
ERROR:  conflicting or redundant options
LINE 1: ...ATE COLLATION coll_dup_chk (DETERMINISTIC = TRUE, DETERMINIS...
                                                             ^
-- VERSION
CREATE COLLATION coll_dup_chk (VERSION = '1', VERSION = "NONSENSE", LOCALE = '');
ERROR:  conflicting or redundant options
LINE 1: CREATE COLLATION coll_dup_chk (VERSION = '1', VERSION = "NON...
                                                      ^
-- LOCALE conflicts with LC_COLLATE and LC_CTYPE
CREATE COLLATION coll_dup_chk (LC_COLLATE = "POSIX", LC_CTYPE = "POSIX", LOCALE = '');
ERROR:  conflicting or redundant options
DETAIL:  LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
-- LOCALE conflicts with LC_COLLATE
CREATE COLLATION coll_dup_chk (LC_COLLATE = "POSIX", LOCALE = '');
ERROR:  conflicting or redundant options
DETAIL:  LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
-- LOCALE conflicts with LC_CTYPE
CREATE COLLATION coll_dup_chk (LC_CTYPE = "POSIX", LOCALE = '');
ERROR:  conflicting or redundant options
DETAIL:  LOCALE cannot be specified together with LC_COLLATE or LC_CTYPE.
-- FROM conflicts with any other option
CREATE COLLATION coll_dup_chk (FROM = "C", VERSION = "1");
ERROR:  conflicting or redundant options
DETAIL:  FROM cannot be specified together with any other options.
-- Regex exact-match optimization should use an index even when the expression
-- and index have different collations, so long as the expression's collation
-- is deterministic.  This example tests what we want because the optimizer
-- does not perceive "C" collation (used by the system catalogs) as identical
-- to "POSIX" collation.
EXPLAIN (COSTS OFF)
SELECT * FROM pg_class WHERE relname ~ '^pg_class$' COLLATE "POSIX";
                        QUERY PLAN                        
----------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_class
   Index Cond: (relname = 'pg_class'::text)
   Filter: (relname ~ '^pg_class$'::text COLLATE "POSIX")
(3 rows)

EXPLAIN (COSTS OFF)
SELECT * FROM pg_class WHERE relname LIKE 'pg\_class' COLLATE "POSIX";
                        QUERY PLAN                        
----------------------------------------------------------
 Index Scan using pg_class_relname_nsp_index on pg_class
   Index Cond: (relname = 'pg_class'::text)
   Filter: (relname ~~ 'pg\_class'::text COLLATE "POSIX")
(3 rows)

--
-- Clean up.  Many of these table names will be re-used if the user is
-- trying to run any platform-specific collation tests later, so we
-- must get rid of them.
--
DROP SCHEMA collate_tests CASCADE;
NOTICE:  drop cascades to 20 other objects
DETAIL:  drop cascades to table collate_test1
drop cascades to table collate_test_like
drop cascades to table collate_test2
drop cascades to type testdomain_p
drop cascades to table collate_test4
drop cascades to table collate_test5
drop cascades to table collate_test10
drop cascades to view collview1
drop cascades to view collview2
drop cascades to view collview3
drop cascades to type testdomain
drop cascades to function vc(text)
drop cascades to function dup(anyelement)
drop cascades to table collate_test20
drop cascades to table collate_test21
drop cascades to table collate_test22
drop cascades to collation builtin_c
drop cascades to collation mycoll2
drop cascades to table collate_test23
drop cascades to view collate_on_int