23.2. 콜레이션 지원

23.2. 콜레이션 지원
이전	위로	23장. 로컬라이제이션	처음으로	다음

23.2.1. 개념
23.2.2. 콜레이션 관리

콜레이션 기능으로 칼럼별 및 연산별 데이터의 정렬 순서 및 문자 분류 동작을 지정할 수 있다. 이것은 데이터베이스 생성 후 LC_COLLATE 및 LC_CTYPE 설정을 변경하지 못하도록 하는 제한을 완화한다.

23.2.1. 개념

개념적으로 콜레이션 가능한 데이터 타입의 모든 표현식은 콜레이션을 갖고 있다. (콜레이션 가능한 내장된 데이터 타입은 text 및 varchar, char이다. 사용자 정의된 베이스 타입은 콜레이션 가능으로 표시될 수 있으며, 콜레이션 가능한 데이터 타입의 도메인도 콜레이션이 가능하다.) 표현식이 칼럼 참조인 경우 표현식의 콜레이션은 칼럼이 정의한 콜레이션이다. 표현식이 상수인 경우 콜레이션은 상수 데이터 타입의 기본 콜레이션이다. 좀 더 복잡한 표현식의 콜레이션은 아래 설명대로 입력 콜레이션으로부터 결정된다.

표현식의 콜레이션은 데이터베이스에 대해 정의된 로케일(locale) 설정을 의미하는 “기본” 콜레이션이 될 수 있다. 표현식의 콜레이션을 결정되지 않은 상태로 하는 것도 가능하다. 이런 경우 콜레이션을 알아야 하는 정렬 명령 및 기타 명령은 실패한다.

데이터베이스 시스템이 정렬 또는 문자 분류를 수행해야 하는 경우 입력 표현식의 콜레이션이 사용된다. 이것은, 예를 들면 ORDER BY 절 및 함수 또는 < 같은 연산자 호출을 사용할 때 결정된다. ORDER BY 절에 적용되는 콜레이션은 단순히 정렬 키의 콜레이션이다. 함수 또는 연산자 호출에 적용되는 콜레이션은 아래 설명된 인자로부터 결정된다. 비교 연산자 외에, 콜레이션은 lower 및 upper, initcap 같이 대소문자 사이를 변환하는 함수와 그리고 패턴 일치 및 to_char와 관련 함수를 사용할 때 결정된다.

함수 또는 연산자 호출의 경우 인자 콜레이션 검사로 결정된 콜레이션이 작업 성능을 위해 런타임에 사용된다. 함수나 연산자 호출의 결과가 콜레이션 가능한 데이터 타입인 경우, 콜레이션에 대한 정보가 필요한 주변 표현식이 있을 때 콜레이션도 파싱 시 함수 또는 연산자 표현식의 정의된 콜레이션으로 사용된다.

표현식의 콜레이션 결정은 암시적 또는 명시적일 수 있다. 이 차이는 복수의 서로 다른 콜레이션이 표현식에 나타나는 경우 콜레이션이 결합되는 방식에 영향을 준다. COLLATE 절을 사용하면 명시적 콜레이션이 결정되고, 그 외 모든 콜레이션 파생은 암시적이다. 예를 들면, 함수 호출에서 복수의 콜레이션을 결합해야 하는 경우 다음과 같은 규칙이 사용된다.

입력 표현식이 명시적 콜레이션을 결정하는 경우 입력 표현식에서 명시적으로 파생된 모든 콜레이션은 동일해야 한다. 그렇지 않으면 에러가 발생한다. 명시적으로 결정된 콜레이션은 콜레이션 결합의 결과물이다.
그 외에는, 모든 입력 표현식은 동일한 암시적 콜레이션을 결정하거나 기본 콜레이션을 사용해야 한다. 기본값이 아닌 콜레이션은 콜레이션 결합의 결과물이다. 그 외에는 기본 콜레이션이다.
입력 표현식 사이에 기본이 아닌 임시적 콜레이션의 충돌이 있을 경우 결합은 불확정적인 콜레이션을 쓰는 것으로 간주된다. 호출하려는 특수한 함수가 적용해야 할 콜레이션에 대한 지식을 필요로 하지 않는 경우에 한하여 이것은 에러 조건이 아니다. 그 외에는 런타임 시 에러가 발생된다.

예를 들어, 다음과 같은 테이블 정의를 생각해 보자.

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);

그러면,

SELECT a < 'foo' FROM test1;

이 구문에서 < 비교는, 표현식이 명시적으로 결정된 콜레이션을 기본 콜레이션과 결합하기 때문에 de_DE 규칙에 따라 수행된다. 그러나,

SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

이 구문에서는 명시적 콜레이션이 암시적 콜레이션을 오버라이드하므로 fr_FR 규칙으로 비교가 수행된다. 게다가,

SELECT a < b FROM test1;

이 구문에서는 a 및 b 칼럼의 암시적 콜레이션이 충돌하므로 어떤 콜레이션을 적용할 것인지 파서로 결정할 수 없다. < 연산자는 어떤 콜레이션을 사용할 것인지 알 필요가 없으므로, 결과적으로 에러가 발생한다. 시적 콜레이션 지정자를 입력 표현식에 추가하면 이 에러가 해결된다. 따라서,

SELECT a < b COLLATE "de_DE" FROM test1;

또는 동일하게, 다음과 같이 할 수 있다.

SELECT a COLLATE "de_DE" < b FROM test1;

즉, 구조상 동일한,

SELECT a || b FROM test1;

이 구문은 || 연산자가 콜레이션에 대해 무심하므로 결과적으로 에러가 발생한다. 이 결과는 콜레이션과 무관하게 동일하다.

또한 함수 또는 연산자의 결합된 입력 표현식에 할당된 콜레이션은 함수 또는 연산자가 콜레이션 가능한 데이터 타입의 결과를 전달하는 경우, 함수 또는 연산자의 결과에 적용되는 것으로 간주된다. 따라서,

SELECT * FROM test1 ORDER BY a || 'foo';

이 구문은 de_DE 규칙에 따라 정렬된다. 그러나 이 쿼리는,

SELECT * FROM test1 ORDER BY a || b;

|| 연산자는 콜레이션에 대해 알고 있을 필요가 없지만 ORDER BY 절은 그렇게 하므로 결과적으로 에러가 발생한다. 이전과 같이, 명시적 콜레이션 지정자를 사용하면 충돌을 해결할 수 있다.

SELECT * FROM test1 ORDER BY a || b COLLATE "fr_FR";

23.2.2. 콜레이션 관리

A collation is an SQL schema object that maps an SQL name to locales provided by libraries installed in the operating system. A collation definition has a provider that specifies which library supplies the locale data. One standard provider name is libc, which uses the locales provided by the operating system C library. These are the locales that most tools provided by the operating system use. Another provider is icu, which uses the external ICU library. ICU locales can only be used if support for ICU was configured when PostgreSQL was built.

A collation object provided by libc maps to a combination of LC_COLLATE and LC_CTYPE settings, as accepted by the setlocale() system library call. (As the name would suggest, the main purpose of a collation is to set LC_COLLATE, which controls the sort order. But it is rarely necessary in practice to have an LC_CTYPE setting that is different from LC_COLLATE, so it is more convenient to collect these under one concept than to create another infrastructure for setting LC_CTYPE per expression.) Also, a libc collation is tied to a character set encoding (see 23.3절). 다른 인코딩으로 동일한 콜레이션 이름이 존재할 수도 있다.

A collation object provided by icu maps to a named collator provided by the ICU library. ICU does not support separate “collate” and “ctype” settings, so they are always the same. Also, ICU collations are independent of the encoding, so there is always only one ICU collation of a given name in a database.

23.2.2.1. 표준 콜레이션

모든 플랫폼에서 이름이 default 및 C, POSIX인 콜레이션을 사용할 수 있다. 추가 콜레이션은 운영 체제 지원에 따라 사용할 수 있다. default 콜레이션은 데이터베이스 생성 시에 지정된 LC_COLLATE 및 LC_CTYPE 값을 선택한다. C 및 POSIX 콜레이션 둘 다 ASCII 문자 “A” ~ “Z”만 글자로 처리되고 문자 코드 바이트 값으로 엄격하게 정렬이 되는 “전형적인 C” 동작을 지정한다.

덧붙여, UTF8 인코딩인 경우, SQL 표준 콜레이션 이름은 ucs_basic이다. 이것은 다른 이름으로 C 이며, 유니코드 문자 정렬과 같다.

23.2.2.2. 미리 정의된 콜레이션

하나의 프로그램에서 (newlocale 관련 함수를 이용해서) 여러 로케일을 사용할 수 있는 운영 체제인 경우나, ICU 라이브러리를 이용할 수 있는 환경이라면, initdb 명령을 이용해 데이터베이스 클러스터가 초기화되면, 당시에 운영 체제에서 찾은 모든 로케일에 따라 콜레이션으로 시스템 카탈로그 pg_collation을 채운다.

To inspect the currently available locales, use the query SELECT * FROM pg_collation, or the command \dOS+ in psql.

23.2.2.2.1. libc Collations

예를 들면, 운영 체제는 de_DE.utf8이라는 이름의 로케일(locale)을 제공할 수도 있다. 그러면 initdb는 LC_COLLATE 및 LC_CTYPE가 둘 다 de_DE.utf8로 설정된 UTF8 인코딩에 대해 de_DE.utf8이라는 콜레이션을 생성한다. 또한 이것은 이름 일부만 추려낸 .utf8 태그를 사용하여 콜레이션을 생성한다. 따라서 작성이 간편하고 인코딩에 대한 이름 의존도가 낮은 de_DE라는 이름으로 콜레이션을 사용할 수도 있다. 그래도 콜레이션 이름의 초기 설정은 플랫폼에 따라 달라진다.

The default set of collations provided by libc map directly to the locales installed in the operating system, which can be listed using the command locale -a. In case a libc collation is needed that has different values for LC_COLLATE and LC_CTYPE, or if new locales are installed in the operating system after the database system was initialized, then a new collation may be created using the CREATE COLLATION command. New operating system locales can also be imported en masse using the pg_import_system_collations() function.

특정한 데이터베이스 내에서 데이터베이스의 인코딩을 사용하는 콜레이션만 중요하다. pg_collation의 다른 항목은 무시된다. 따라서 de_DE 같이 일부만 추려낸 콜레이션 이름은 전역적으로는 고유하지 않더라도 지정된 데이터베이스 내에서는 고유한 것으로 간주될 수 있다. 데이터베이스 인코딩을 다른 것으로 변경하기로 결정한 경우 변경 사항이 적은 추려낸 콜레이션 이름을 사용하는 것이 권장한다. 그러나 default 및 C, POSIX 콜레이션은 데이터베이스 인코딩과 무관하게 사용할 수 있다.

PostgreSQL은 속성이 동일한 경우에도 별개의 콜레이션 개체는 호환되지 않는 것으로 간주한다. 따라서 예를 들면,

SELECT a COLLATE "C" < b COLLATE "POSIX" FROM test1;

이 구문은 C 및 POSIX 콜레이션의 동작이 동일하더라도 에러가 발생된다. 따라서 추려낸 콜레이션 이름과 추려내지 않은 콜레이션 이름의 혼용은 권장하지 않는다.

23.2.2.2.2. ICU Collations

With ICU, it is not sensible to enumerate all possible locale names. ICU uses a particular naming system for locales, but there are many more ways to name a locale than there are actually distinct locales. initdb uses the ICU APIs to extract a set of distinct locales to populate the initial set of collations. Collations provided by ICU are created in the SQL environment with names in BCP 47 language tag format, with a “private use” extension -x-icu appended, to distinguish them from libc locales.

Here are some example collations that might be created:

de-x-icu

German collation, default variant

de-AT-x-icu

German collation for Austria, default variant

(There are also, say, de-DE-x-icu or de-CH-x-icu, but as of this writing, they are equivalent to de-x-icu.)

und-x-icu (for “undefined”)

ICU “root” collation. Use this to get a reasonable language-agnostic sort order.

Some (less frequently used) encodings are not supported by ICU. When the database encoding is one of these, ICU collation entries in pg_collation are ignored. Attempting to use one will draw an error along the lines of “collation "de-x-icu" for encoding "WIN874" does not exist”.

23.2.2.3. Creating New Collation Objects

If the standard and predefined collations are not sufficient, users can create their own collation objects using the SQL command CREATE COLLATION.

The standard and predefined collations are in the schema pg_catalog, like all predefined objects. User-defined collations should be created in user schemas. This also ensures that they are saved by pg_dump.

23.2.2.3.1. libc Collations

New libc collations can be created like this:

CREATE COLLATION german (provider = libc, locale = 'de_DE');

The exact values that are acceptable for the locale clause in this command depend on the operating system. On Unix-like systems, the command locale -a will show a list.

Since the predefined libc collations already include all collations defined in the operating system when the database instance is initialized, it is not often necessary to manually create new ones. Reasons might be if a different naming system is desired (in which case see also 23.2.2.3.3절) or if the operating system has been upgraded to provide new locale definitions (in which case see also pg_import_system_collations()).

23.2.2.3.2. ICU Collations

ICU allows collations to be customized beyond the basic language+country set that is preloaded by initdb. Users are encouraged to define their own collation objects that make use of these facilities to suit the sorting behavior to their requirements. See http://userguide.icu-project.org/locale and http://userguide.icu-project.org/collation/api for information on ICU locale naming. The set of acceptable names and attributes depends on the particular ICU version.

Here are some examples:

CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de-u-co-phonebk'); CREATE COLLATION "de-u-co-phonebk-x-icu" (provider = icu, locale = 'de@collation=phonebook');

German collation with phone book collation type

The first example selects the ICU locale using a “language tag” per BCP 47. The second example uses the traditional ICU-specific locale syntax. The first style is preferred going forward, but it is not supported by older ICU versions.

Note that you can name the collation objects in the SQL environment anything you want. In this example, we follow the naming style that the predefined collations use, which in turn also follow BCP 47, but that is not required for user-defined collations.

CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = 'und-u-co-emoji'); CREATE COLLATION "und-u-co-emoji-x-icu" (provider = icu, locale = '@collation=emoji');

Root collation with Emoji collation type, per Unicode Technical Standard #51

Observe how in the traditional ICU locale naming system, the root locale is selected by an empty string.

CREATE COLLATION latinlast (provider = icu, locale = 'en-u-kr-grek-latn'); CREATE COLLATION latinlast (provider = icu, locale = 'en@colReorder=grek-latn');

Sort Greek letters before Latin ones. (The default is Latin before Greek.)

CREATE COLLATION upperfirst (provider = icu, locale = 'en-u-kf-upper'); CREATE COLLATION upperfirst (provider = icu, locale = 'en@colCaseFirst=upper');

Sort upper-case letters before lower-case letters. (The default is lower-case letters first.)

CREATE COLLATION special (provider = icu, locale = 'en-u-kf-upper-kr-grek-latn'); CREATE COLLATION special (provider = icu, locale = 'en@colCaseFirst=upper;colReorder=grek-latn');

Combines both of the above options.

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true'); CREATE COLLATION numeric (provider = icu, locale = 'en@colNumeric=yes');

Numeric ordering, sorts sequences of digits by their numeric value, for example: A-21 < A-123 (also known as natural sort).

See Unicode Technical Standard #35 and BCP 47 for details. The list of possible collation types (co subtag) can be found in the CLDR repository. The ICU Locale Explorer can be used to check the details of a particular locale definition. The examples using the k* subtags require at least ICU version 54.

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), in order for such collations to act in a truly case- or accent-insensitive manner, they also need to be declared as not deterministic in CREATE COLLATION; see 23.2.2.4절. Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

참고

By design, ICU will accept almost any string as a locale name and match it to the closest locale it can provide, using the fallback procedure described in its documentation. Thus, there will be no direct feedback if a collation specification is composed using features that the given ICU installation does not actually support. It is therefore recommended to create application-level test cases to check that the collation definitions satisfy one's requirements.

23.2.2.3.3. Copying Collations

The command CREATE COLLATION can also be used to create a new collation from an existing collation, which can be useful to be able to use operating-system-independent collation names in applications, create compatibility names, or use an ICU-provided collation under a more readable name. For example:

CREATE COLLATION german FROM "de_DE";
CREATE COLLATION french FROM "fr-x-icu";

23.2.2.4. Nondeterministic Collations

A collation is either deterministic or nondeterministic. A deterministic collation uses deterministic comparisons, which means that it considers strings to be equal only if they consist of the same byte sequence. Nondeterministic comparison may determine strings to be equal even if they consist of different bytes. Typical situations include case-insensitive comparison, accent-insensitive comparison, as well as comparison of strings in different Unicode normal forms. It is up to the collation provider to actually implement such insensitive comparisons; the deterministic flag only determines whether ties are to be broken using bytewise comparison. See also Unicode Technical Standard 10 for more information on the terminology.

To create a nondeterministic collation, specify the property deterministic = false to CREATE COLLATION, for example:

CREATE COLLATION ndcoll (provider = icu, locale = 'und', deterministic = false);

This example would use the standard Unicode collation in a nondeterministic way. In particular, this would allow strings in different normal forms to be compared correctly. More interesting examples make use of the ICU customization facilities explained above. For example:

CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

All standard and predefined collations are deterministic, all user-defined collations are deterministic by default. While nondeterministic collations give a more “correct” behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Note, in particular, that B-tree cannot use deduplication with indexes that use a nondeterministic collation. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.

작은 정보

To deal with text in different Unicode normalization forms, it is also an option to use the functions/expressions normalize and is normalized to preprocess or check the strings, instead of using nondeterministic collations. There are different trade-offs for each approach.