'\" t .\" Title: CREATE COLLATION .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 16.2 Documentation .\" Source: PostgreSQL 16.2 .\" Language: English .\" .TH "CREATE COLLATION" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" CREATE_COLLATION \- define a new collation .SH "SYNOPSIS" .sp .nf CREATE COLLATION [ IF NOT EXISTS ] \fIname\fR ( [ LOCALE = \fIlocale\fR, ] [ LC_COLLATE = \fIlc_collate\fR, ] [ LC_CTYPE = \fIlc_ctype\fR, ] [ PROVIDER = \fIprovider\fR, ] [ DETERMINISTIC = \fIboolean\fR, ] [ RULES = \fIrules\fR, ] [ VERSION = \fIversion\fR ] ) CREATE COLLATION [ IF NOT EXISTS ] \fIname\fR FROM \fIexisting_collation\fR .fi .SH "DESCRIPTION" .PP \fBCREATE COLLATION\fR defines a new collation using the specified operating system locale settings, or by copying an existing collation\&. .PP To be able to create a collation, you must have CREATE privilege on the destination schema\&. .SH "PARAMETERS" .PP IF NOT EXISTS .RS 4 Do 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\&. .RE .PP \fIname\fR .RS 4 The 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\&.) .RE .PP \fIlocale\fR .RS 4 The locale name for this collation\&. See Section\ \&24.2.2.3.1 and Section\ \&24.2.2.3.2 for details\&. .sp If \fIprovider\fR is libc, this is a shortcut for setting LC_COLLATE and LC_CTYPE at once\&. If you specify \fIlocale\fR, you cannot specify either of those parameters\&. .RE .PP \fIlc_collate\fR .RS 4 If \fIprovider\fR is libc, use the specified operating system locale for the LC_COLLATE locale category\&. .RE .PP \fIlc_ctype\fR .RS 4 If \fIprovider\fR is libc, use the specified operating system locale for the LC_CTYPE locale category\&. .RE .PP \fIprovider\fR .RS 4 Specifies the provider to use for locale services associated with this collation\&. Possible values are icu (if the server was built with ICU support) or libc\&. libc is the default\&. See Section\ \&24.1.4 for details\&. .RE .PP DETERMINISTIC .RS 4 Specifies 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 LC_COLLATE setting \fIand\fR set the collation to not deterministic here\&. .sp Nondeterministic collations are only supported with the ICU provider\&. .RE .PP \fIrules\fR .RS 4 Specifies additional collation rules to customize the behavior of the collation\&. This is supported for ICU only\&. See Section\ \&24.2.3.4 for details\&. .RE .PP \fIversion\fR .RS 4 Specifies 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 \fBpg_upgrade\fR for copying the version from an existing installation\&. .sp See also ALTER COLLATION (\fBALTER_COLLATION\fR(7)) for how to handle collation version mismatches\&. .RE .PP \fIexisting_collation\fR .RS 4 The 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\&. .RE .SH "NOTES" .PP \fBCREATE COLLATION\fR takes a SHARE ROW EXCLUSIVE lock, which is self\-conflicting, on the pg_collation system catalog, so only one \fBCREATE COLLATION\fR command can run at a time\&. .PP Use \fBDROP COLLATION\fR to remove user\-defined collations\&. .PP See Section\ \&24.2.2.3 for more information on how to create collations\&. .PP When using the libc collation provider, the locale must be applicable to the current database encoding\&. See CREATE DATABASE (\fBCREATE_DATABASE\fR(7)) for the precise rules\&. .SH "EXAMPLES" .PP To create a collation from the operating system locale fr_FR\&.utf8 (assuming the current database encoding is UTF8): .sp .if n \{\ .RS 4 .\} .nf CREATE COLLATION french (locale = \*(Aqfr_FR\&.utf8\*(Aq); .fi .if n \{\ .RE .\} .PP To create a collation using the ICU provider using German phone book sort order: .sp .if n \{\ .RS 4 .\} .nf CREATE COLLATION german_phonebook (provider = icu, locale = \*(Aqde\-u\-co\-phonebk\*(Aq); .fi .if n \{\ .RE .\} .PP To create a collation using the ICU provider, based on the root ICU locale, with custom rules: .sp .if n \{\ .RS 4 .\} .nf CREATE COLLATION custom (provider = icu, locale = \*(Aqund\*(Aq, rules = \*(Aq&V << w <<< W\*(Aq); .fi .if n \{\ .RE .\} .sp See Section\ \&24.2.3.4 for further details and examples on the rules syntax\&. .PP To create a collation from an existing collation: .sp .if n \{\ .RS 4 .\} .nf CREATE COLLATION german FROM "de_DE"; .fi .if n \{\ .RE .\} .sp This can be convenient to be able to use operating\-system\-independent collation names in applications\&. .SH "COMPATIBILITY" .PP There is a \fBCREATE COLLATION\fR statement in the SQL standard, but it is limited to copying an existing collation\&. The syntax to create a new collation is a PostgreSQL extension\&. .SH "SEE ALSO" ALTER COLLATION (\fBALTER_COLLATION\fR(7)), DROP COLLATION (\fBDROP_COLLATION\fR(7))