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:

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