{"Entry":{"collection":"locale","key":"provider-default","name":"default","aliases":[],"metadata":{"aliases":[],"category":"Collation provider identities","content_hash":"fbaed3466732f88f2a146a4d749e5e045a663356959c2e00fbc533b35a014d8b","imported_at":"2026-09-30T00:40:33.788387+08:00","name":"default","name_zh":"","slug":"provider-default","summary":"Database-default collation selector."}},"Definition":{"Collection":"locale","Key":"provider-default","SourceDatabase":"center","Version":"18","SourceTable":"collation_encoding","SourceKey":"provider-default","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"attributes":{"catalog_code":"d","provider":"default","scope":"Source provider identity; installation/build availability is separate"},"comparison_data":{"catalog_code":"d","provider":"default","scope":"Source provider identity; installation/build availability is separate"},"comparison_hash":"c5db26a0d9420c95af858233ae3c48df967ff89d6bea33cf1569f99f882abff9","description":["Database-default collation selector."],"facts":[{"label":"Provider","value":"default"},{"label":"Catalog code","value":"d"},{"label":"Scope","value":"Source provider identity; installation/build availability is separate"}],"manual_html":"\u003cdiv class=\"sect2\" id=\"COLLATION-CONCEPTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e23.2.1. Concepts \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eConceptually, every expression of a collatable data type has a collation. (The built-in collatable data types are \u003ccode class=\"type\"\u003etext\u003c/code\u003e, \u003ccode class=\"type\"\u003evarchar\u003c/code\u003e, and \u003ccode class=\"type\"\u003echar\u003c/code\u003e. User-defined base types can also be marked collatable, and of course a \u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e\u003c/a\u003e\u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\"\u003edomain\u003c/a\u003e over a collatable data type is collatable.) If the expression is a column reference, the collation of the expression is the defined collation of the column. If the expression is a constant, the collation is the default collation of the data type of the constant. The collation of a more complex expression is derived from the collations of its inputs, as described below.\u003c/p\u003e\n\u003cp\u003eThe collation of an expression can be the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edefault\u003c/span\u003e”\u003c/span\u003e collation, which means the locale settings defined for the database. It is also possible for an expression's collation to be indeterminate. In such cases, ordering operations and other operations that need to know the collation will fail.\u003c/p\u003e\n\u003cp\u003eWhen the database system has to perform an ordering or a character classification, it uses the collation of the input expression. This happens, for example, with \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses and function or operator calls such as \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e. The collation to apply for an \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clause is simply the collation of the sort key. The collation to apply for a function or operator call is derived from the arguments, as described below. In addition to comparison operators, collations are taken into account by functions that convert between lower and upper case letters, such as \u003ccode class=\"function\"\u003elower\u003c/code\u003e, \u003ccode class=\"function\"\u003eupper\u003c/code\u003e, and \u003ccode class=\"function\"\u003einitcap\u003c/code\u003e; by pattern matching operators; and by \u003ccode class=\"function\"\u003eto_char\u003c/code\u003e and related functions.\u003c/p\u003e\n\u003cp\u003eFor a function or operator call, the collation that is derived by examining the argument collations is used at run time for performing the specified operation. If the result of the function or operator call is of a collatable data type, the collation is also used at parse time as the defined collation of the function or operator expression, in case there is a surrounding expression that requires knowledge of its collation.\u003c/p\u003e\n\u003cp\u003eThe \u003cem class=\"firstterm\"\u003ecollation derivation\u003c/em\u003e of an expression can be implicit or explicit. This distinction affects how collations are combined when multiple different collations appear in an expression. An explicit collation derivation occurs when a \u003ccode class=\"literal\"\u003eCOLLATE\u003c/code\u003e clause is used; all other collation derivations are implicit. When multiple collations need to be combined, for example in a function call, the following rules are used:\u003c/p\u003e\n\u003cdiv class=\"orderedlist\"\u003e\n\u003col class=\"orderedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised. If any explicitly derived collation is present, that is the result of the collation combination.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eOtherwise, all input expressions must have the same implicit collation derivation or the default collation. If any non-default collation is present, that is the result of the collation combination. Otherwise, the result is the default collation.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation. This is not an error condition unless the particular function being invoked requires knowledge of the collation it should apply. If it does, an error will be raised at run-time.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003c/div\u003e\n\u003cp\u003eFor example, consider this table definition:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE test1 (\n    a text COLLATE \"de_DE\",\n    b text COLLATE \"es_ES\",\n    ...\n);\n\u003c/pre\u003e\n\u003cp\u003eThen in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; 'foo' FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e comparison is performed according to \u003ccode class=\"literal\"\u003ede_DE\u003c/code\u003e rules, because the expression combines an implicitly derived collation with the default collation. But in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; ('foo' COLLATE \"fr_FR\") FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe comparison is performed using \u003ccode class=\"literal\"\u003efr_FR\u003c/code\u003e rules, because the explicit collation derivation overrides the implicit one. Furthermore, given\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe parser cannot determine which collation to apply, since the \u003ccode class=\"structfield\"\u003ea\u003c/code\u003e and \u003ccode class=\"structfield\"\u003eb\u003c/code\u003e columns have conflicting implicit collations. Since the \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e operator does need to know which collation to use, this will result in an error. The error can be resolved by attaching an explicit collation specifier to either input expression, thus:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; b COLLATE \"de_DE\" FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eor equivalently\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a COLLATE \"de_DE\" \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eOn the other hand, the structurally similar case\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a || b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003edoes not result in an error, because the \u003ccode class=\"literal\"\u003e||\u003c/code\u003e operator does not care about collations: its result is the same regardless of the collation.\u003c/p\u003e\n\u003cp\u003eThe collation assigned to a function or operator's combined input expressions is also considered to apply to the function or operator's result, if the function or operator delivers a result of a collatable data type. So, in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || 'foo';\n\u003c/pre\u003e\n\u003cp\u003ethe ordering will be done according to \u003ccode class=\"literal\"\u003ede_DE\u003c/code\u003e rules. But this query:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || b;\n\u003c/pre\u003e\n\u003cp\u003eresults in an error, because even though the \u003ccode class=\"literal\"\u003e||\u003c/code\u003e operator doesn't need to know a collation, the \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clause does. As before, the conflict can be resolved with an explicit collation specifier:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || b COLLATE \"fr_FR\";\n\u003c/pre\u003e\n\u003c/div\u003e","manual_path":"/docs/18/collation.html#COLLATION-CONCEPTS","related":[],"release":{"catalog_fingerprint":"65c93d6048ef30e61023a84f9680fa6a92b1c383b7eb226741170077eb078502","channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sections":[],"signature":"","sources":[{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18 English manual","path":"collation.html","sha256":"c98cbca2b2235a896f7fe438a88a99b8d6b9d1f50bdb49fe2794e9982a50797f","url":"/docs/18/collation.html#COLLATION-CONCEPTS"}],"tables":[]},"ManualEvidence":{"manual_path":"/docs/18/collation.html#COLLATION-CONCEPTS","release":{"catalog_fingerprint":"65c93d6048ef30e61023a84f9680fa6a92b1c383b7eb226741170077eb078502","channel":"stable","label":"18.6","major":"18","ref":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"},"sources":[{"label":"Matching PostgreSQL source archive","sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},{"label":"PostgreSQL 18 English manual","path":"collation.html","sha256":"c98cbca2b2235a896f7fe438a88a99b8d6b9d1f50bdb49fe2794e9982a50797f","url":"/docs/18/collation.html#COLLATION-CONCEPTS"}]},"MeasuredEvidence":{}},"Text":{"Collection":"locale","Key":"provider-default","SourceDatabase":"center","Version":"18","Locale":"en","Title":"default","Summary":"Database-default collation selector.","BodyHTML":"\u003cdiv id=\"COLLATION-CONCEPTS\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3\u003e23.2.1. Concepts \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eConceptually, every expression of a collatable data type has a collation. (The built-in collatable data types are \u003ccode\u003etext\u003c/code\u003e, \u003ccode\u003evarchar\u003c/code\u003e, and \u003ccode\u003echar\u003c/code\u003e. User-defined base types can also be marked collatable, and of course a \u003ca href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" rel=\"nofollow\"\u003e\u003c/a\u003e\u003ca href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\" rel=\"nofollow\"\u003edomain\u003c/a\u003e over a collatable data type is collatable.) If the expression is a column reference, the collation of the expression is the defined collation of the column. If the expression is a constant, the collation is the default collation of the data type of the constant. The collation of a more complex expression is derived from the collations of its inputs, as described below.\u003c/p\u003e\n\u003cp\u003eThe collation of an expression can be the \u003cspan\u003e“\u003cspan\u003edefault\u003c/span\u003e”\u003c/span\u003e collation, which means the locale settings defined for the database. It is also possible for an expression\u0026#39;s collation to be indeterminate. In such cases, ordering operations and other operations that need to know the collation will fail.\u003c/p\u003e\n\u003cp\u003eWhen the database system has to perform an ordering or a character classification, it uses the collation of the input expression. This happens, for example, with \u003ccode\u003eORDER BY\u003c/code\u003e clauses and function or operator calls such as \u003ccode\u003e\u0026lt;\u003c/code\u003e. The collation to apply for an \u003ccode\u003eORDER BY\u003c/code\u003e clause is simply the collation of the sort key. The collation to apply for a function or operator call is derived from the arguments, as described below. In addition to comparison operators, collations are taken into account by functions that convert between lower and upper case letters, such as \u003ccode\u003elower\u003c/code\u003e, \u003ccode\u003eupper\u003c/code\u003e, and \u003ccode\u003einitcap\u003c/code\u003e; by pattern matching operators; and by \u003ccode\u003eto_char\u003c/code\u003e and related functions.\u003c/p\u003e\n\u003cp\u003eFor a function or operator call, the collation that is derived by examining the argument collations is used at run time for performing the specified operation. If the result of the function or operator call is of a collatable data type, the collation is also used at parse time as the defined collation of the function or operator expression, in case there is a surrounding expression that requires knowledge of its collation.\u003c/p\u003e\n\u003cp\u003eThe \u003cem\u003ecollation derivation\u003c/em\u003e of an expression can be implicit or explicit. This distinction affects how collations are combined when multiple different collations appear in an expression. An explicit collation derivation occurs when a \u003ccode\u003eCOLLATE\u003c/code\u003e clause is used; all other collation derivations are implicit. When multiple collations need to be combined, for example in a function call, the following rules are used:\u003c/p\u003e\n\u003cdiv\u003e\n\u003col\u003e\n\u003cli\u003e\n\u003cp\u003eIf any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised. If any explicitly derived collation is present, that is the result of the collation combination.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eOtherwise, all input expressions must have the same implicit collation derivation or the default collation. If any non-default collation is present, that is the result of the collation combination. Otherwise, the result is the default collation.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli\u003e\n\u003cp\u003eIf there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation. This is not an error condition unless the particular function being invoked requires knowledge of the collation it should apply. If it does, an error will be raised at run-time.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003c/div\u003e\n\u003cp\u003eFor example, consider this table definition:\u003c/p\u003e\n\u003cpre\u003eCREATE TABLE test1 (\n    a text COLLATE \u0026#34;de_DE\u0026#34;,\n    b text COLLATE \u0026#34;es_ES\u0026#34;,\n    ...\n);\n\u003c/pre\u003e\n\u003cp\u003eThen in\u003c/p\u003e\n\u003cpre\u003eSELECT a \u0026lt; \u0026#39;foo\u0026#39; FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe \u003ccode\u003e\u0026lt;\u003c/code\u003e comparison is performed according to \u003ccode\u003ede_DE\u003c/code\u003e rules, because the expression combines an implicitly derived collation with the default collation. But in\u003c/p\u003e\n\u003cpre\u003eSELECT a \u0026lt; (\u0026#39;foo\u0026#39; COLLATE \u0026#34;fr_FR\u0026#34;) FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe comparison is performed using \u003ccode\u003efr_FR\u003c/code\u003e rules, because the explicit collation derivation overrides the implicit one. Furthermore, given\u003c/p\u003e\n\u003cpre\u003eSELECT a \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe parser cannot determine which collation to apply, since the \u003ccode\u003ea\u003c/code\u003e and \u003ccode\u003eb\u003c/code\u003e columns have conflicting implicit collations. Since the \u003ccode\u003e\u0026lt;\u003c/code\u003e operator does need to know which collation to use, this will result in an error. The error can be resolved by attaching an explicit collation specifier to either input expression, thus:\u003c/p\u003e\n\u003cpre\u003eSELECT a \u0026lt; b COLLATE \u0026#34;de_DE\u0026#34; FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eor equivalently\u003c/p\u003e\n\u003cpre\u003eSELECT a COLLATE \u0026#34;de_DE\u0026#34; \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eOn the other hand, the structurally similar case\u003c/p\u003e\n\u003cpre\u003eSELECT a || b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003edoes not result in an error, because the \u003ccode\u003e||\u003c/code\u003e operator does not care about collations: its result is the same regardless of the collation.\u003c/p\u003e\n\u003cp\u003eThe collation assigned to a function or operator\u0026#39;s combined input expressions is also considered to apply to the function or operator\u0026#39;s result, if the function or operator delivers a result of a collatable data type. So, in\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM test1 ORDER BY a || \u0026#39;foo\u0026#39;;\n\u003c/pre\u003e\n\u003cp\u003ethe ordering will be done according to \u003ccode\u003ede_DE\u003c/code\u003e rules. But this query:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM test1 ORDER BY a || b;\n\u003c/pre\u003e\n\u003cp\u003eresults in an error, because even though the \u003ccode\u003e||\u003c/code\u003e operator doesn\u0026#39;t need to know a collation, the \u003ccode\u003eORDER BY\u003c/code\u003e clause does. As before, the conflict can be resolved with an explicit collation specifier:\u003c/p\u003e\n\u003cpre\u003eSELECT * FROM test1 ORDER BY a || b COLLATE \u0026#34;fr_FR\u0026#34;;\n\u003c/pre\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"5dbd471608565b68fd942b2c0d3746e3c4fb71061e612f71b1d24b5a582afa75","Payload":{"description":["Database-default collation selector."],"manual_html":"\u003cdiv class=\"sect2\" id=\"COLLATION-CONCEPTS\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch3 class=\"title\"\u003e23.2.1. Concepts \u003c/h3\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eConceptually, every expression of a collatable data type has a collation. (The built-in collatable data types are \u003ccode class=\"type\"\u003etext\u003c/code\u003e, \u003ccode class=\"type\"\u003evarchar\u003c/code\u003e, and \u003ccode class=\"type\"\u003echar\u003c/code\u003e. User-defined base types can also be marked collatable, and of course a \u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e\u003c/a\u003e\u003ca class=\"glossterm\" href=\"/docs/18/glossary.html#GLOSSARY-DOMAIN\" title=\"Domain\"\u003edomain\u003c/a\u003e over a collatable data type is collatable.) If the expression is a column reference, the collation of the expression is the defined collation of the column. If the expression is a constant, the collation is the default collation of the data type of the constant. The collation of a more complex expression is derived from the collations of its inputs, as described below.\u003c/p\u003e\n\u003cp\u003eThe collation of an expression can be the \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003edefault\u003c/span\u003e”\u003c/span\u003e collation, which means the locale settings defined for the database. It is also possible for an expression's collation to be indeterminate. In such cases, ordering operations and other operations that need to know the collation will fail.\u003c/p\u003e\n\u003cp\u003eWhen the database system has to perform an ordering or a character classification, it uses the collation of the input expression. This happens, for example, with \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clauses and function or operator calls such as \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e. The collation to apply for an \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clause is simply the collation of the sort key. The collation to apply for a function or operator call is derived from the arguments, as described below. In addition to comparison operators, collations are taken into account by functions that convert between lower and upper case letters, such as \u003ccode class=\"function\"\u003elower\u003c/code\u003e, \u003ccode class=\"function\"\u003eupper\u003c/code\u003e, and \u003ccode class=\"function\"\u003einitcap\u003c/code\u003e; by pattern matching operators; and by \u003ccode class=\"function\"\u003eto_char\u003c/code\u003e and related functions.\u003c/p\u003e\n\u003cp\u003eFor a function or operator call, the collation that is derived by examining the argument collations is used at run time for performing the specified operation. If the result of the function or operator call is of a collatable data type, the collation is also used at parse time as the defined collation of the function or operator expression, in case there is a surrounding expression that requires knowledge of its collation.\u003c/p\u003e\n\u003cp\u003eThe \u003cem class=\"firstterm\"\u003ecollation derivation\u003c/em\u003e of an expression can be implicit or explicit. This distinction affects how collations are combined when multiple different collations appear in an expression. An explicit collation derivation occurs when a \u003ccode class=\"literal\"\u003eCOLLATE\u003c/code\u003e clause is used; all other collation derivations are implicit. When multiple collations need to be combined, for example in a function call, the following rules are used:\u003c/p\u003e\n\u003cdiv class=\"orderedlist\"\u003e\n\u003col class=\"orderedlist\"\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf any input expression has an explicit collation derivation, then all explicitly derived collations among the input expressions must be the same, otherwise an error is raised. If any explicitly derived collation is present, that is the result of the collation combination.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eOtherwise, all input expressions must have the same implicit collation derivation or the default collation. If any non-default collation is present, that is the result of the collation combination. Otherwise, the result is the default collation.\u003c/p\u003e\n\u003c/li\u003e\n\u003cli class=\"listitem\"\u003e\n\u003cp\u003eIf there are conflicting non-default implicit collations among the input expressions, then the combination is deemed to have indeterminate collation. This is not an error condition unless the particular function being invoked requires knowledge of the collation it should apply. If it does, an error will be raised at run-time.\u003c/p\u003e\n\u003c/li\u003e\n\u003c/ol\u003e\n\u003c/div\u003e\n\u003cp\u003eFor example, consider this table definition:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE TABLE test1 (\n    a text COLLATE \"de_DE\",\n    b text COLLATE \"es_ES\",\n    ...\n);\n\u003c/pre\u003e\n\u003cp\u003eThen in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; 'foo' FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e comparison is performed according to \u003ccode class=\"literal\"\u003ede_DE\u003c/code\u003e rules, because the expression combines an implicitly derived collation with the default collation. But in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; ('foo' COLLATE \"fr_FR\") FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe comparison is performed using \u003ccode class=\"literal\"\u003efr_FR\u003c/code\u003e rules, because the explicit collation derivation overrides the implicit one. Furthermore, given\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003ethe parser cannot determine which collation to apply, since the \u003ccode class=\"structfield\"\u003ea\u003c/code\u003e and \u003ccode class=\"structfield\"\u003eb\u003c/code\u003e columns have conflicting implicit collations. Since the \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e operator does need to know which collation to use, this will result in an error. The error can be resolved by attaching an explicit collation specifier to either input expression, thus:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a \u0026lt; b COLLATE \"de_DE\" FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eor equivalently\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a COLLATE \"de_DE\" \u0026lt; b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003eOn the other hand, the structurally similar case\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT a || b FROM test1;\n\u003c/pre\u003e\n\u003cp\u003edoes not result in an error, because the \u003ccode class=\"literal\"\u003e||\u003c/code\u003e operator does not care about collations: its result is the same regardless of the collation.\u003c/p\u003e\n\u003cp\u003eThe collation assigned to a function or operator's combined input expressions is also considered to apply to the function or operator's result, if the function or operator delivers a result of a collatable data type. So, in\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || 'foo';\n\u003c/pre\u003e\n\u003cp\u003ethe ordering will be done according to \u003ccode class=\"literal\"\u003ede_DE\u003c/code\u003e rules. But this query:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || b;\n\u003c/pre\u003e\n\u003cp\u003eresults in an error, because even though the \u003ccode class=\"literal\"\u003e||\u003c/code\u003e operator doesn't need to know a collation, the \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e clause does. As before, the conflict can be resolved with an explicit collation specifier:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eSELECT * FROM test1 ORDER BY a || b COLLATE \"fr_FR\";\n\u003c/pre\u003e\n\u003c/div\u003e","related":[],"sections":[],"tables":[]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}