{"Entry":{"collection":"locale","key":"provider-builtin","name":"builtin","aliases":[],"metadata":{"aliases":[],"category":"Collation provider identities","content_hash":"11355b46a77004baf447edc1b8e4ab2b4f172daf8c239389472f9a79ed03b038","derived_content_hash":"494b3d90a529f72dbb152b80f64727bcc3fc56aa54d40a5808cbb6670035a075","imported_at":"2026-09-30T00:40:33.784318+08:00","name":"builtin","name_zh":"","slug":"provider-builtin","summary":"builtin collation provider."}},"Definition":{"Collection":"locale","Key":"provider-builtin","SourceDatabase":"center","Version":"18","SourceTable":"collation_encoding","SourceKey":"provider-builtin","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"aliases":[],"attributes":{"catalog_code":"b","provider":"builtin","scope":"Source provider identity; installation/build availability is separate"},"comparison_data":{"catalog_code":"b","provider":"builtin","scope":"Source provider identity; installation/build availability is separate"},"comparison_hash":"d0ee87b3545cc68aafaa491d16e6d3a73ddc2ded39dd20ba234b922cd06fd397","description":["builtin collation provider."],"description_zh":"排序规则与编码 provider-builtin。","description_zh_html":"\u003cp\u003e排序规则与编码 provider-builtin。\u003c/p\u003e\u003cdl\u003e\u003cdt\u003e适用范围\u003c/dt\u003e\u003cdd\u003e\u003ccode\u003eSource provider identity; installation/build availability is separate\u003c/code\u003e\u003c/dd\u003e\u003c/dl\u003e\u003ch3\u003e同版本中文手册节选\u003c/h3\u003e\u003cp\u003e\n    从概念上讲，每个支持排序规则的数据类型的表达式都有一个排序规则。（内置的支持排序规则的数据类型包括 \u003ccode class=\"type\"\u003etext\u003c/code\u003e、\u003ccode class=\"type\"\u003evarchar\u003c/code\u003e 和 \u003ccode class=\"type\"\u003echar\u003c/code\u003e。用户定义的基础类型也可以标记为支持排序规则，当然，建立在支持排序规则的数据类型之上的\u003ca class=\"glossterm\" href=\"/zh/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e\u003cem class=\"glossterm\"\u003e\u003c/em\u003e\u003c/a\u003e\u003cem class=\"glossterm\"\u003e\u003ca class=\"glossterm\" href=\"/zh/docs/18/glossary.html#GLOSSARY-DOMAIN\"\u003e域\u003c/a\u003e\u003c/em\u003e也支持排序规则。）如果表达式是列引用，则该表达式的排序规则就是该列定义的排序规则。如果表达式是常量，则其排序规则就是该常量数据类型的默认排序规则。更复杂表达式的排序规则则按下文所述，从其输入表达式的排序规则推导出来。\n   \u003c/p\u003e\u003cp\u003e\n    表达式的排序规则可以是\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e默认\u003c/span\u003e”\u003c/span\u003e排序规则，这表示为数据库定义的区域设置。表达式的排序规则也可能是不确定的。在这种情况下，排序操作以及其他需要知道排序规则的操作都会失败。\n   \u003c/p\u003e\u003cp\u003e\n    当数据库系统必须执行排序或字符分类时，它会使用输入表达式的排序规则。例如，这会发生在 \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e 子句以及函数或操作符调用（如\n    \u003ccode class=\"literal\"\u003e\u0026lt;\u003c/code\u003e）中。应用于 \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e 子句的排序规则就是排序键的排序规则。应用于函数或操作符调用的排序规则则按下文所述，从参数中推导出来。除了比较操作符之外，在大小写之间进行转换的函数（例如 \u003ccode class=\"function\"\u003elower\u003c/code\u003e、\u003ccode class=\"function\"\u003eupper\u003c/code\u003e 和\n    \u003ccode class=\"function\"\u003einitcap\u003c/code\u003e）、模式匹配操作符，以及 \u003ccode class=\"function\"\u003eto_char\u003c/code\u003e\n    及相关函数，也都会考虑排序规则。\n   \u003c/p\u003e\u003cp\u003e\n    对于函数或操作符调用，通过检查参数排序规则推导出的排序规则，会在运行时用于执行指定操作。如果该函数或操作符调用的结果属于支持排序规则的数据类型，那么在解析时它也会被用作该函数或操作符表达式的已定义排序规则，以便在外围表达式需要知道其排序规则时使用。\n   \u003c/p\u003e\u003cp\u003e\u003ca href=\"/zh/docs/18/collation.html#COLLATION-CONCEPTS\"\u003e查看完整中文手册与上下文\u003c/a\u003e。\u003c/p\u003e","facts":[{"label":"Provider","value":"builtin"},{"label":"Catalog code","value":"b"},{"label":"Scope","value":"Source provider identity; installation/build availability is separate"}],"facts_zh":[{"label":"提供者","value":"builtin"},{"label":"目录代码","value":"b"},{"label":"范围","value":"源码中的提供者标识；安装或构建中是否可用另行判断"}],"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":[],"sections_zh":[],"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":[],"tables_zh":[],"zh_from":{"authored_translation":"source-derived-structured-summary","body_sha256":"5ab56ea97e93ce72443a3fa627d29c4a55cb55b0cf3f6d7a7643eda06f6280aa","kind":"same-version-chinese-manual-with-source-derived-structured-summary","manual":{"anchor":"COLLATION-CONCEPTS","build_id":107,"extraction":"concept-introduction","fragment_sha256":"a57382d34f6fe21fe2e1d3927479580bbac33099cf1802452a272b78e65aab98","locale":"zh-Hans","page_key":"collation.html","source_hash":"0bbcf85c650ba958c8d947a7a456ac42ada62afdf57cc030f62d93f756661fac","source_revision":"unknown:rendered-translation-baseline","translated_from_revision":null,"translation_revision":"git:b1b500221dbe5437cfca004d6b04d3f6e422765d;html-sha256:08e987a66af27b98444a8057c256314eafa13611a3fb9e888214fc436824244b","url":"/zh/docs/18/collation.html#COLLATION-CONCEPTS","version":"18"},"runtime_rerun":false,"source_facts_revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f"}},"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-builtin","SourceDatabase":"center","Version":"18","Locale":"en","Title":"builtin","Summary":"builtin collation provider.","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":"cdc98f55eb911b13e228af1cf05f3addcc8059aab4667919ab2ef1fcf3b6577d","Payload":{"description":["builtin collation provider."],"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":"en","Fallback":false,"Versions":["17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}