About locale

• 1: Locale handling in Vertica
• 2: Specifying locale: long form
• 3: Specifying locale: short form
• 4: Supported locales
• 5: Locale and UTF-8 support
• 6: Locale-aware string functions

=> set locale to '';
INFO 2567:  Canonical locale: 'en_US_POSIX'
Standard collation: 'LEN'
English (United States, Computer)
SET
=> \locale en_US@collation=binary;
INFO 2567:  Canonical locale: 'en_US'
Standard collation: 'LEN_KBINARY'
English (United States)
=>  \locale
en_US@collation-binary;

1 - Locale handling in Vertica

The following sections describes how Vertica handles locale.

Session locale

Locale is session-scoped and applies only to queries executed in that session. You cannot specify locale for individual queries. When you start a session it obtains its locale from the configuration parameter DefaultSessionLocale.

Query restrictions

The following restrictions apply when queries are run with locale other than the default en_US@collation=binary:

• When one or more of the left-side NOT IN columns is CHAR or VARCHAR, multi-column NOT IN subqueries are not supported . For example:

  => CREATE TABLE test (x VARCHAR(10), y INT);
  => SELECT ... FROM test WHERE (x,y) NOT IN (SELECT ...);
     ERROR: Multi-expression NOT IN subquery is not supported because a left
     hand expression could be NULL
  

  Note

  Even if columns test.x and test.y have a NOT NULL constraint, an error occurs.

• If the outer query contains a GROUP BY clause on a CHAR or VARCHAR column, correlated HAVING clause subqueries are not supported. In the following example, the GROUP BY x in the outer query causes the error:

  => DROP TABLE test CASCADE;
  => CREATE TABLE test (x VARCHAR(10));
  => SELECT COUNT(*) FROM test t GROUP BY x HAVING x
       IN (SELECT x FROM test WHERE t.x||'a' = test.x||'a' );
     ERROR: subquery uses ungrouped column "t.x" from outer query
  

• Subqueries that use analytic functions in the HAVING clause are not supported. For example:

  => DROP TABLE test CASCADE;
  => CREATE TABLE test (x VARCHAR(10));
  => SELECT MAX(x)OVER(PARTITION BY 1 ORDER BY 1) FROM test
       GROUP BY x HAVING x IN (SELECT MAX(x) FROM test);
     ERROR: Analytics query with having clause expression that involves
     aggregates and subquery is not supported
  

Collation and projections

Projection data is sorted according to the default en_US@collation=binary collation. Thus, regardless of the session setting, issuing the following command creates a projection sorted by col1 according to the binary collation:

=> CREATE PROJECTION p1 AS SELECT * FROM table1 ORDER BY col1;

In such cases, straße and strasse are not stored near each other on disk.

Sorting by binary collation also means that sort optimizations do not work in locales other than binary. Vertica returns the following warning if you create tables or projections in a non-binary locale:

WARNING:  Projections are always created and persisted in the default
Vertica locale. The current locale is de_DE

Non-binary locale input handling

When the locale is non-binary, Vertica uses the COLLATION function to transform input to a binary string that sorts in the proper order.

This transformation increases the number of bytes required for the input according to this formula:

result_column_width = input_octet_width * CollationExpansion + 4

The default value of configuration parameter CollationExpansion is 5.

Character data type handling

• CHAR fields are displayed as fixed length, including any trailing spaces. When CHAR fields are processed internally, they are first stripped of trailing spaces. For VARCHAR fields, trailing spaces are usually treated as significant characters; however, trailing spaces are ignored when sorting or comparing either type of character string field using a non-binary locale.

• The maximum length parameter for VARCHAR and CHAR data type refers to the number of octets (bytes) that can be stored in that field and not number of characters. When using multi-byte UTF-8 characters, the fields must be sized to accommodate from 1 to 4 bytes per character, depending on the data.

2 - Specifying locale: long form

Vertica supports long forms that specify the collation keyword. Vertica extends long-form processing to accept collation arguments.

Syntax

[language][_script][_country][_variant][@collation-spec]

Parameters

language

A two- or three-letter lowercase code for a particular language. For example, Spanish is es English is en and French is fr. The two-letter language code uses the ISO-639 standard.

_script

An optional four-letter script code that follows the language code. If specified, it should be a valid script code as listed on the Unicode ISO 15924 Registry.

_country

A specific language convention within a generic language for a specific country or region. For example, French is spoken in many countries, but the currencies are different in each country. To allow for these differences among specific geographical, political, or cultural regions, locales are specified by two-letter, uppercase codes. For example, FR represents France and CA represents Canada. The two letter country code uses the ISO-3166 standard.

_variant

Differences may also appear in language conventions used within the same country. For example, the Euro currency is used in several European countries while the individual country's currency is still in circulation. To handle variations inside a language and country pair, add a third code, the variant code. The variant code is arbitrary and completely application-specific. ICU adds _EURO to its locale designations for locales that support the Euro currency. Variants can have any number of underscored key words. For example, EURO_WIN is a variant for the Euro currency on a Windows computer.

Another use of the variant code is to designate the Collation (sorting order) of a locale. For instance, the es__TRADITIONAL locale uses the traditional sorting order which is different from the default modern sorting of Spanish.

@collation-spec

Vertica only supports the keyword collation, as follows:

@collation=collation-type[;arg]...

Collation can specify one or more semicolon-delimited arguments, described below.

collation-type is set to one of the following values:

• big5han: Pinyin ordering for Latin, big5 charset ordering for CJK characters (used in Chinese).

• dict: For a dictionary-style ordering (such as in Sinhala).

• direct: Hindi variant.

• gb2312/gb2312han: Pinyin ordering for Latin, gb2312han charset ordering for CJK characters (used in Chinese).

• phonebook: For a phonebook-style ordering (such as in German).

• pinyin: Pinyin ordering for Latin and for CJK characters; that is, an ordering for CJK characters based on a character-by-character transliteration into a pinyin (used in Chinese).

• reformed: Reformed collation (such as in Swedish).

• standard: The default ordering for each language. For root it is [UCA] order; for each other locale it is the same as UCA (Unicode Collation Algorithm) ordering except for appropriate modifications to certain characters for that language. The following are additional choices for certain locales; they have effect only in certain locales.

• stroke: Pinyin ordering for Latin, stroke order for CJK characters (used in Chinese) not supported.

• traditional: For a traditional-style ordering (such as in Spanish).

• unihan: Pinyin ordering for Latin, Unihan radical-stroke ordering for CJK characters (used in Chinese) not supported.

• binary: Vertica default, providing UTF-8 octet ordering.

Notes:

• Collations might default to root, the ICU default collation.

• Invalid values of the collation keyword and its synonyms do not cause an error. For example, the following does not generate an error. It simply ignores the invalid value:

  => \locale en_GB@collation=xyz
  INFO 2567:  Canonical locale: 'en_GB@collation=xyz'
  Standard collation: 'LEN'
  English (United Kingdom, collation=xyz)
  

For more about collation options, see Unicode Locale Data Markup Language (LDML).

Collation arguments

collation can specify one or more of the following arguments :

Locale processing notes

• Incorrect locale strings are accepted if the prefix can be resolved to a known locale version.

  For example, the following works because the language can be resolved:

  => \locale en_XX
  INFO 2567:  Canonical locale: 'en_XX'
  Standard collation: 'LEN'
  English (XX)
  

  The following does not work because the language cannot be resolved:

  => \locale xx_XX
  xx_XX: invalid locale identifier
  

• POSIX-type locales such as en_US.UTF-8 work to some extent in that the encoding part "UTF-8" is ignored.

• Vertica uses the icu4c-4_2_1 library to support basic locale/collation processing with some extensions. This does not currently meet current standards for locale processing (https://tools.ietf.org/html/rfc5646).

Examples

Specify German locale as used in Germany (de), with phonebook-style collation:

=> \locale de_DE@collation=phonebook
INFO 2567:  Canonical locale: 'de_DE@collation=phonebook'
Standard collation: 'KPHONEBOOK_LDE'
German (Germany, collation=Phonebook Sort Order)
Deutsch (Deutschland, Sortierung=Telefonbuch-Sortierregeln)

Specify German locale as used in Germany (de), with phonebook-style collation and strength set to secondary:

=> \locale de_DE@collation=phonebook;colStrength=secondary
INFO 2567:  Canonical locale: 'de_DE@collation=phonebook'
Standard collation: 'KPHONEBOOK_LDE_S2'
German (Germany, collation=Phonebook Sort Order)
Deutsch (Deutschland, Sortierung=Telefonbuch-Sortierregeln)

3 - Specifying locale: short form

Vertica accepts locales in short form. You can use the short form to specify the locale and keyname pair/value names.

To determine the short form for a locale, type in the long form and view the last line of INFO, as follows:

\locale frINFO:  Locale: 'fr'
INFO:    French
INFO:    français
INFO:  Short form: 'LFR'

Examples

Specify en (English) locale:

\locale LENINFO:  Locale: 'en'
INFO:  English
INFO:  Short form: 'LEN'

Specify German locale as used in Germany (de), with phonebook-style collation:

\locale LDE_KPHONEBOOKINFO:  Locale: 'de@collation=phonebook'
INFO:  German (collation=Phonebook Sort Order)
INFO:  Deutsch (Sortierung=Telefonbuch-Sortierregeln)
INFO:  Short form: 'KPHONEBOOK_LDE'

Specify German locale as used in Germany (de), with phonebook-style collation:

\locale LDE_KPHONEBOOK_S2INFO:  Locale: 'de@collation=phonebook'
INFO:  German (collation=Phonebook Sort Order)
INFO:  Deutsch (Sortierung=Telefonbuch-Sortierregeln)
INFO:  Short form: 'KPHONEBOOK_LDE_S2'

4 - Supported locales

The following are the supported locale strings for Vertica. Each locale can optionally have a list of key/value pairs (see Specifying locale: long form).

5 - Locale and UTF-8 support

Vertica supports Unicode Transformation Format-8, or UTF8, where 8 equals 8-bit. UTF-8 is a variable-length character encoding for Unicode created by Ken Thompson and Rob Pike. UTF-8 can represent any universal character in the Unicode standard. Initial encoding of byte codes and character assignments for UTF-8 coincides with ASCII. Thus, UTF8 requires little or no change for software that handles ASCII but preserves other values.

Vertica database servers expect to receive all data in UTF-8, and Vertica outputs all data in UTF-8. The ODBC API operates on data in UCS-2 on Windows systems, and normally UTF-8 on Linux systems. JDBC and ADO.NET APIs operate on data in UTF-16. Client drivers automatically convert data to and from UTF-8 when sending to and receiving data from Vertica using API calls. The drivers do not transform data loaded by executing a COPY or COPY LOCAL statement.

UTF-8 string functions

The following string functions treat VARCHAR arguments as UTF-8 strings (when USING OCTETS is not specified) regardless of locale setting.

6 - Locale-aware string functions

Vertica provides string functions to support internationalization. Unless otherwise specified, these string functions can optionally specify whether VARCHAR arguments should be interpreted as octet (byte) sequences, or as (locale-aware) sequences of characters. Specify this information by adding the parameter USING OCTETS and USING CHARACTERS (default) to the function.

The following table lists all string functions that are locale-aware: