{"Entry":{"collection":"sql","key":"create-collation","name":"CREATE COLLATION","aliases":["createcollation"],"metadata":{"aliases":["createcollation"],"changed_in":["10","12","16"],"changes":[{"from":"9.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"9.1"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["CREATE COLLATION [ IF NOT EXISTS ] name (","[ LC_CTYPE = lc_ctype, ]","[ PROVIDER = provider, ]","[ VERSION = version ]","CREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation"],"removed":[]},"to":"10"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["[ DETERMINISTIC = boolean, ]","[ VERSION = version ]"],"removed":[]},"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["notes"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ RULES = rules, ]","[ VERSION = version ]"],"removed":[]},"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"}],"content_hash":"d747b3e3c34813070a3de4a1201a063772693d888d7c82dea5e0b0068a11f252","editorial":{},"first_version":"9.1","group":"type","imported_at":"2026-09-30T17:43:37.388678+08:00","last_version":"20","name":"CREATE COLLATION","object":"COLLATION","position":3002,"present_in":["9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new collation","purpose_zh":"","related":["alter-collation","drop-collation"],"slug":"create-collation","source_rev":"a709ab85","synopsis":"CREATE COLLATION [ IF NOT EXISTS ] name (\n[ LOCALE = locale, ]\n[ LC_COLLATE = lc_collate, ]\n[ LC_CTYPE = lc_ctype, ]\n[ PROVIDER = provider, ]\n[ DETERMINISTIC = boolean, ]\n[ RULES = rules, ]\n[ VERSION = version ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-collation","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-collation","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATECOLLATION","file":"sql-createcollation.html","lang":"en","name":"CREATE COLLATION","purpose":"define a new collation","purpose_zh":"","related":["alter-collation","drop-collation"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e defines a new collation using the specified operating system locale settings, or by copying an existing collation.\u003c/p\u003e\u003cp\u003eTo be able to create a collation, you must have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege on the destination schema.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDo not throw an error if a collation with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing collation is anything like the one that would have been created.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the collation. The collation name can be schema-qualified. If it is not, the collation is defined in the current schema. The collation name must be unique within that schema. (The system catalogs can contain collations with the same name for other encodings, but these are ignored if the database encoding does not match.)\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe locale name for this collation. See \u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-LIBC\" title=\"23.2.2.3.1. libc Collations\"\u003eSection 23.2.2.3.1\u003c/a\u003e and \u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-ICU\" title=\"23.2.2.3.2. ICU Collations\"\u003eSection 23.2.2.3.2\u003c/a\u003e for details.\u003c/p\u003e\u003cp\u003eIf \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e, this is a shortcut for setting \u003ccode class=\"symbol\"\u003eLC_COLLATE\u003c/code\u003e and \u003ccode class=\"symbol\"\u003eLC_CTYPE\u003c/code\u003e at once. If you specify \u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e, you cannot specify either of those parameters.\u003c/p\u003e\u003cp\u003eIf \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"literal\"\u003ebuiltin\u003c/code\u003e, then \u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e must be specified and set to either \u003ccode class=\"literal\"\u003eC\u003c/code\u003e, \u003ccode class=\"literal\"\u003eC.UTF-8\u003c/code\u003e or \u003ccode class=\"literal\"\u003ePG_UNICODE_FAST\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_collate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e, use the specified operating system locale for the \u003ccode class=\"symbol\"\u003eLC_COLLATE\u003c/code\u003e locale category.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_ctype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIf \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e is \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e, use the specified operating system locale for the \u003ccode class=\"symbol\"\u003eLC_CTYPE\u003c/code\u003e locale category.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the provider to use for locale services associated with this collation. Possible values are \u003ccode class=\"literal\"\u003ebuiltin\u003c/code\u003e, \u003ccode class=\"literal\"\u003eicu\u003c/code\u003e (if the server was built with ICU support) or \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e. \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e is the default. See \u003ca href=\"/docs/18/locale.html#LOCALE-PROVIDERS\" title=\"23.1.4. Locale Providers\"\u003eSection 23.1.4\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDETERMINISTIC\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether the collation should use deterministic comparisons. The default is true. A deterministic comparison considers strings that are not byte-wise equal to be unequal even if they are considered logically equal by the comparison. PostgreSQL breaks ties using a byte-wise comparison. Comparison that is not deterministic can make the collation be, say, case- or accent-insensitive. For that, you need to choose an appropriate \u003ccode class=\"literal\"\u003eLOCALE\u003c/code\u003e setting \u003cspan class=\"emphasis\"\u003e\u003cem\u003eand\u003c/em\u003e\u003c/span\u003e set the collation to not deterministic here.\u003c/p\u003e\u003cp\u003eNondeterministic collations are only supported with the ICU provider.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003erules\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies additional collation rules to customize the behavior of the collation. This is supported for ICU only. See \u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" title=\"23.2.3.4. ICU Tailoring Rules\"\u003eSection 23.2.3.4\u003c/a\u003e for details.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eversion\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies the version string to store with the collation. Normally, this should be omitted, which will cause the version to be computed from the actual version of the collation as provided by the operating system. This option is intended to be used by \u003ccode class=\"command\"\u003epg_upgrade\u003c/code\u003e for copying the version from an existing installation.\u003c/p\u003e\u003cp\u003eSee also \u003ca href=\"/docs/18/sql-altercollation.html\" title=\"ALTER COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e for how to handle collation version mismatches.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexisting_collation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of an existing collation to copy. The new collation will have the same properties as the existing one, but it will be an independent object.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e takes a \u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e lock, which is self-conflicting, on the \u003ccode class=\"structname\"\u003epg_collation\u003c/code\u003e system catalog, so only one \u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e command can run at a time.\u003c/p\u003e\u003cp\u003eUse \u003ccode class=\"command\"\u003eDROP COLLATION\u003c/code\u003e to remove user-defined collations.\u003c/p\u003e\u003cp\u003eSee \u003ca href=\"/docs/18/collation.html#COLLATION-CREATE\" title=\"23.2.2.3. Creating New Collation Objects\"\u003eSection 23.2.2.3\u003c/a\u003e for more information on how to create collations.\u003c/p\u003e\u003cp\u003eWhen using the \u003ccode class=\"literal\"\u003elibc\u003c/code\u003e collation provider, the locale must be applicable to the current database encoding. See \u003ca href=\"/docs/18/sql-createdatabase.html\" title=\"CREATE DATABASE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE DATABASE\u003c/span\u003e\u003c/a\u003e for the precise rules.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eTo create a collation from the operating system locale \u003ccode class=\"literal\"\u003efr_FR.utf8\u003c/code\u003e (assuming the current database encoding is \u003ccode class=\"literal\"\u003eUTF8\u003c/code\u003e):\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION french (locale = 'fr_FR.utf8');\n\u003c/pre\u003e\u003cp\u003eTo create a collation using the ICU provider using German phone book sort order:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');\n\u003c/pre\u003e\u003cp\u003eTo create a collation using the ICU provider, based on the root ICU locale, with custom rules:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION custom (provider = icu, locale = 'und', rules = '\u0026amp;V \u0026lt;\u0026lt; w \u0026lt;\u0026lt;\u0026lt; W');\n\u003c/pre\u003e\u003cp\u003eSee \u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" title=\"23.2.3.4. ICU Tailoring Rules\"\u003eSection 23.2.3.4\u003c/a\u003e for further details and examples on the rules syntax.\u003c/p\u003e\u003cp\u003eTo create a collation from an existing collation:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION german FROM \"de_DE\";\n\u003c/pre\u003e\u003cp\u003eThis can be convenient to be able to use operating-system-independent collation names in applications.\u003c/p\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003eThere is a \u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e statement in the SQL standard, but it is limited to copying an existing collation. The syntax to create a new collation is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-collation/?v=18\" title=\"ALTER COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-collation/?v=18\" title=\"DROP COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP COLLATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE COLLATION [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    [ LOCALE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e, ]\n    [ LC_COLLATE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_collate\u003c/code\u003e\u003c/em\u003e, ]\n    [ LC_CTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_ctype\u003c/code\u003e\u003c/em\u003e, ]\n    [ PROVIDER = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e, ]\n    [ DETERMINISTIC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e, ]\n    [ RULES = \u003cem class=\"replaceable\"\u003e\u003ccode\u003erules\u003c/code\u003e\u003c/em\u003e, ]\n    [ VERSION = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eversion\u003c/code\u003e\u003c/em\u003e ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexisting_collation\u003c/code\u003e\u003c/em\u003e","synopsis_text":"CREATE COLLATION [ IF NOT EXISTS ] name (\n[ LOCALE = locale, ]\n[ LC_COLLATE = lc_collate, ]\n[ LC_CTYPE = lc_ctype, ]\n[ PROVIDER = provider, ]\n[ DETERMINISTIC = boolean, ]\n[ RULES = rules, ]\n[ VERSION = version ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-collation","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE COLLATION","Summary":"定义一种新排序规则","BodyHTML":"\u003cpre\u003eCREATE COLLATION [ IF NOT EXISTS ] name (\n[ LOCALE = locale, ]\n[ LC_COLLATE = lc_collate, ]\n[ LC_CTYPE = lc_ctype, ]\n[ PROVIDER = provider, ]\n[ DETERMINISTIC = boolean, ]\n[ RULES = rules, ]\n[ VERSION = version ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE COLLATION\u003c/code\u003e使用指定的操作系统区域设置，或者通过复制现有排序规则，定义一个新的排序规则。\u003c/p\u003e\u003cp\u003e要创建排序规则，你必须拥有目标模式上的 \u003ccode\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名排序规则已经存在，则不要抛出错误。在这种情况下会发出一条通知。请注意，不保证现有的排序规则与本应创建出的排序规则有任何相似之处。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e排序规则的名称，可以使用模式限定。若未使用模式限定，则该排序规则会在当前模式中定义。排序规则名称在该模式内必须唯一。（系统目录中可以包含针对其他编码的同名排序规则，但如果数据库编码不匹配，则会忽略它们。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该排序规则的区域设置名称。详见\u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-LIBC\" rel=\"nofollow\"\u003e第 23.2.2.3.1 节\u003c/a\u003e和\u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-ICU\" rel=\"nofollow\"\u003e第 23.2.2.3.2 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果\u003cem\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode\u003elibc\u003c/code\u003e，这相当于同时设置\u003ccode\u003eLC_COLLATE\u003c/code\u003e和 \u003ccode\u003eLC_CTYPE\u003c/code\u003e。如果指定了 \u003cem\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e，则不能再指定这两个参数中的任何一个。\u003c/p\u003e\u003cp\u003e如果\u003cem\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode\u003ebuiltin\u003c/code\u003e，则必须指定\u003cem\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e，且其值只能是 \u003ccode\u003eC\u003c/code\u003e、\u003ccode\u003eC.UTF-8\u003c/code\u003e或 \u003ccode\u003ePG_UNICODE_FAST\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elc_collate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果\u003cem\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode\u003elibc\u003c/code\u003e，则对\u003ccode\u003eLC_COLLATE\u003c/code\u003e区域类别使用指定的操作系统区域设置。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elc_ctype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果\u003cem\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode\u003elibc\u003c/code\u003e，则对\u003ccode\u003eLC_CTYPE\u003c/code\u003e区域类别使用指定的操作系统区域设置。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定与该排序规则关联的区域设置服务所使用的提供程序。可能的值是 \u003ccode\u003ebuiltin\u003c/code\u003e、\u003ccode\u003eicu\u003c/code\u003e（如果服务器在构建时启用了 ICU 支持）或\u003ccode\u003elibc\u003c/code\u003e。默认值是\u003ccode\u003elibc\u003c/code\u003e。详见\u003ca href=\"/docs/18/locale.html#LOCALE-PROVIDERS\" rel=\"nofollow\"\u003e第 23.1.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDETERMINISTIC\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定该排序规则是否应使用确定性比较。默认值为 true。确定性比较认为，凡不是按字节完全相等的字符串，即使比较在逻辑上认为它们相等，也应视为不相等。\u003cspan\u003ePostgreSQL\u003c/span\u003e会使用按字节比较来打破平局。非确定性比较可以使排序规则例如不区分大小写或重音。要实现这一点，你需要选择合适的\u003ccode\u003eLOCALE\u003c/code\u003e设置\u003cspan\u003e\u003cem\u003e并\u003c/em\u003e\u003c/span\u003e在此处将排序规则设为非确定性。\u003c/p\u003e\u003cp\u003e非确定性排序规则仅受 ICU 提供程序支持。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003erules\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定额外的定制规则，以自定义该排序规则的行为。这仅受 ICU 支持。详见\u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" rel=\"nofollow\"\u003e第 23.2.3.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eversion\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定随该排序规则存储的版本字符串。通常应省略该选项，这样版本就会根据操作系统提供的该排序规则实际版本来计算。此选项旨在供 \u003ccode\u003epg_upgrade\u003c/code\u003e从现有安装中复制版本时使用。\u003c/p\u003e\u003cp\u003e另见\u003ca href=\"/docs/18/sql-altercollation.html\" title=\"ALTER COLLATION\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e，了解如何处理排序规则版本不匹配的问题。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eexisting_collation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要复制的现有排序规则的名称。新的排序规则将具有与现有排序规则相同的属性，但它是一个独立对象。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE COLLATION\u003c/code\u003e会在\u003ccode\u003epg_collation\u003c/code\u003e 系统目录上获取一个\u003ccode\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁。该锁是自冲突的，因此一次只能运行一条\u003ccode\u003eCREATE COLLATION\u003c/code\u003e 命令。\u003c/p\u003e\u003cp\u003e使用\u003ccode\u003eDROP COLLATION\u003c/code\u003e可移除用户定义的排序规则。\u003c/p\u003e\u003cp\u003e关于如何创建排序规则的更多信息，见\u003ca href=\"/docs/18/collation.html#COLLATION-CREATE\" rel=\"nofollow\"\u003e第 23.2.2.3 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e使用\u003ccode\u003elibc\u003c/code\u003e排序规则提供者时，区域设置必须适用于当前数据库编码。有关精确规则，请参见\u003ca href=\"/docs/18/sql-createdatabase.html\" title=\"CREATE DATABASE\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE DATABASE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e从操作系统区域设置\u003ccode\u003efr_FR.utf8\u003c/code\u003e创建一种排序规则（假定当前数据库编码是\u003ccode\u003eUTF8\u003c/code\u003e）：\u003c/p\u003e\u003cpre\u003eCREATE COLLATION french (locale = \u0026#39;fr_FR.utf8\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e使用 ICU 提供程序并采用德语电话簿排序顺序创建一种排序规则：\u003c/p\u003e\u003cpre\u003eCREATE COLLATION german_phonebook (provider = icu, locale = \u0026#39;de-u-co-phonebk\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e使用 ICU 提供程序、基于 ICU 根区域设置并带有自定义规则创建一种排序规则：\u003c/p\u003e\u003cpre\u003eCREATE COLLATION custom (provider = icu, locale = \u0026#39;und\u0026#39;, rules = \u0026#39;\u0026amp;V \u0026lt;\u0026lt; w \u0026lt;\u0026lt;\u0026lt; W\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e关于规则语法的更多细节和示例，见\u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" rel=\"nofollow\"\u003e第 23.2.3.4 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e通过复制现有排序规则来创建一种排序规则：\u003c/p\u003e\u003cpre\u003eCREATE COLLATION german FROM \u0026#34;de_DE\u0026#34;;\n\u003c/pre\u003e\u003cp\u003e这样在应用程序中使用与操作系统无关的排序规则名称会比较方便。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e在 SQL 标准中有一个\u003ccode\u003eCREATE COLLATION\u003c/code\u003e 语句，但它仅限于复制现有排序规则。创建新排序规则的语法是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-collation/?v=18\" title=\"ALTER COLLATION\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-collation/?v=18\" title=\"DROP COLLATION\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP COLLATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"388531f0ef1dd1d5a2137969cbdd36ac0a21ebc71eadf4a4794ab8a49606fea1","Payload":{"purpose_zh":"定义一种新排序规则","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e使用指定的操作系统区域设置，或者通过复制现有排序规则，定义一个新的排序规则。\u003c/p\u003e\u003cp\u003e要创建排序规则，你必须拥有目标模式上的 \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eIF NOT EXISTS\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果同名排序规则已经存在，则不要抛出错误。在这种情况下会发出一条通知。请注意，不保证现有的排序规则与本应创建出的排序规则有任何相似之处。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e排序规则的名称，可以使用模式限定。若未使用模式限定，则该排序规则会在当前模式中定义。排序规则名称在该模式内必须唯一。（系统目录中可以包含针对其他编码的同名排序规则，但如果数据库编码不匹配，则会忽略它们。）\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该排序规则的区域设置名称。详见\u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-LIBC\" title=\"23.2.2.3.1. libc 排序规则\"\u003e第 23.2.2.3.1 节\u003c/a\u003e和\u003ca href=\"/docs/18/collation.html#COLLATION-MANAGING-CREATE-ICU\" title=\"23.2.2.3.2. ICU 排序规则\"\u003e第 23.2.2.3.2 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e，这相当于同时设置\u003ccode class=\"symbol\"\u003eLC_COLLATE\u003c/code\u003e和 \u003ccode class=\"symbol\"\u003eLC_CTYPE\u003c/code\u003e。如果指定了 \u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e，则不能再指定这两个参数中的任何一个。\u003c/p\u003e\u003cp\u003e如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode class=\"literal\"\u003ebuiltin\u003c/code\u003e，则必须指定\u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e，且其值只能是 \u003ccode class=\"literal\"\u003eC\u003c/code\u003e、\u003ccode class=\"literal\"\u003eC.UTF-8\u003c/code\u003e或 \u003ccode class=\"literal\"\u003ePG_UNICODE_FAST\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_collate\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e，则对\u003ccode class=\"symbol\"\u003eLC_COLLATE\u003c/code\u003e区域类别使用指定的操作系统区域设置。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_ctype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e如果\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e是\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e，则对\u003ccode class=\"symbol\"\u003eLC_CTYPE\u003c/code\u003e区域类别使用指定的操作系统区域设置。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定与该排序规则关联的区域设置服务所使用的提供程序。可能的值是 \u003ccode class=\"literal\"\u003ebuiltin\u003c/code\u003e、\u003ccode class=\"literal\"\u003eicu\u003c/code\u003e（如果服务器在构建时启用了 ICU 支持）或\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e。默认值是\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e。详见\u003ca href=\"/docs/18/locale.html#LOCALE-PROVIDERS\" title=\"23.1.4. 区域设置提供程序\"\u003e第 23.1.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDETERMINISTIC\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定该排序规则是否应使用确定性比较。默认值为 true。确定性比较认为，凡不是按字节完全相等的字符串，即使比较在逻辑上认为它们相等，也应视为不相等。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e会使用按字节比较来打破平局。非确定性比较可以使排序规则例如不区分大小写或重音。要实现这一点，你需要选择合适的\u003ccode class=\"literal\"\u003eLOCALE\u003c/code\u003e设置\u003cspan class=\"emphasis\"\u003e\u003cem\u003e并\u003c/em\u003e\u003c/span\u003e在此处将排序规则设为非确定性。\u003c/p\u003e\u003cp\u003e非确定性排序规则仅受 ICU 提供程序支持。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003erules\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定额外的定制规则，以自定义该排序规则的行为。这仅受 ICU 支持。详见\u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" title=\"23.2.3.4. ICU 定制规则\"\u003e第 23.2.3.4 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eversion\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定随该排序规则存储的版本字符串。通常应省略该选项，这样版本就会根据操作系统提供的该排序规则实际版本来计算。此选项旨在供 \u003ccode class=\"command\"\u003epg_upgrade\u003c/code\u003e从现有安装中复制版本时使用。\u003c/p\u003e\u003cp\u003e另见\u003ca href=\"/docs/18/sql-altercollation.html\" title=\"ALTER COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e，了解如何处理排序规则版本不匹配的问题。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexisting_collation\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要复制的现有排序规则的名称。新的排序规则将具有与现有排序规则相同的属性，但它是一个独立对象。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e会在\u003ccode class=\"structname\"\u003epg_collation\u003c/code\u003e 系统目录上获取一个\u003ccode class=\"literal\"\u003eSHARE ROW EXCLUSIVE\u003c/code\u003e锁。该锁是自冲突的，因此一次只能运行一条\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e 命令。\u003c/p\u003e\u003cp\u003e使用\u003ccode class=\"command\"\u003eDROP COLLATION\u003c/code\u003e可移除用户定义的排序规则。\u003c/p\u003e\u003cp\u003e关于如何创建排序规则的更多信息，见\u003ca href=\"/docs/18/collation.html#COLLATION-CREATE\" title=\"23.2.2.3. 创建新的排序规则对象\"\u003e第 23.2.2.3 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e使用\u003ccode class=\"literal\"\u003elibc\u003c/code\u003e排序规则提供者时，区域设置必须适用于当前数据库编码。有关精确规则，请参见\u003ca href=\"/docs/18/sql-createdatabase.html\" title=\"CREATE DATABASE\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE DATABASE\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e从操作系统区域设置\u003ccode class=\"literal\"\u003efr_FR.utf8\u003c/code\u003e创建一种排序规则（假定当前数据库编码是\u003ccode class=\"literal\"\u003eUTF8\u003c/code\u003e）：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION french (locale = 'fr_FR.utf8');\n\u003c/pre\u003e\u003cp\u003e使用 ICU 提供程序并采用德语电话簿排序顺序创建一种排序规则：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION german_phonebook (provider = icu, locale = 'de-u-co-phonebk');\n\u003c/pre\u003e\u003cp\u003e使用 ICU 提供程序、基于 ICU 根区域设置并带有自定义规则创建一种排序规则：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION custom (provider = icu, locale = 'und', rules = '\u0026amp;V \u0026lt;\u0026lt; w \u0026lt;\u0026lt;\u0026lt; W');\n\u003c/pre\u003e\u003cp\u003e关于规则语法的更多细节和示例，见\u003ca href=\"/docs/18/collation.html#ICU-TAILORING-RULES\" title=\"23.2.3.4. ICU 定制规则\"\u003e第 23.2.3.4 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e通过复制现有排序规则来创建一种排序规则：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE COLLATION german FROM \"de_DE\";\n\u003c/pre\u003e\u003cp\u003e这样在应用程序中使用与操作系统无关的排序规则名称会比较方便。\u003c/p\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e在 SQL 标准中有一个\u003ccode class=\"command\"\u003eCREATE COLLATION\u003c/code\u003e 语句，但它仅限于复制现有排序规则。创建新排序规则的语法是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-collation/?v=18\" title=\"ALTER COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER COLLATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-collation/?v=18\" title=\"DROP COLLATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP COLLATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE COLLATION [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    [ LOCALE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elocale\u003c/code\u003e\u003c/em\u003e, ]\n    [ LC_COLLATE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_collate\u003c/code\u003e\u003c/em\u003e, ]\n    [ LC_CTYPE = \u003cem class=\"replaceable\"\u003e\u003ccode\u003elc_ctype\u003c/code\u003e\u003c/em\u003e, ]\n    [ PROVIDER = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eprovider\u003c/code\u003e\u003c/em\u003e, ]\n    [ DETERMINISTIC = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eboolean\u003c/code\u003e\u003c/em\u003e, ]\n    [ RULES = \u003cem class=\"replaceable\"\u003e\u003ccode\u003erules\u003c/code\u003e\u003c/em\u003e, ]\n    [ VERSION = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eversion\u003c/code\u003e\u003c/em\u003e ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e FROM \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexisting_collation\u003c/code\u003e\u003c/em\u003e","synopsis_text":"CREATE COLLATION [ IF NOT EXISTS ] name (\n[ LOCALE = locale, ]\n[ LC_COLLATE = lc_collate, ]\n[ LC_CTYPE = lc_ctype, ]\n[ PROVIDER = provider, ]\n[ DETERMINISTIC = boolean, ]\n[ RULES = rules, ]\n[ VERSION = version ]\n)\nCREATE COLLATION [ IF NOT EXISTS ] name FROM existing_collation"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}