SET DEFAULTCOLLATE

The SET DEFAULTCOLLATE statement specifies the collating sequence to use for all columns of data type CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR, or NLONGVARCHAR. The statement offers the following options:

•A null value to default to the numerical order of the current code page

•A path to a file containing alternate collating sequence (ACS) rules

•An International Sorting Rules (ISR) table name

•An International Components for Unicode (ICU) collation name

sort-order ::= path name to an ACS file or the name of an ISR table or a supported ICU collation name

The SET DEFAULTCOLLATE statement offers the convenience of a global session setting. However, an individual column definition can use the COLLATE keyword to set its particular collating sequence. If so, then SET DEFAULTCOLLATE has no effect on that column.

When you provide an ACS file for the sort-order parameter, the following statements apply:

•You must specify a path accessible to the database engine rather than to the calling application.

•The path must be enclosed in single quotation marks.

•The path must be at least 1 character but no more than 255 characters long.

•The path must already exist and must include the name of an ACS file. An ACS file is a 265-byte image of the format used by the MicroKernel Engine. By default, PSQL installs the commonly used ACS file upper.alt in C:\ProgramData\Actian\PSQL\samples. You can also use a custom file. For information on custom files, see User-Defined ACS in PSQL Programmer's Guide.

•Relative paths are allowed and are relative to the DDF directory. Relative paths can contain a period (current directory), double period (parent directory), slash, or any combination of the three. Slash characters in relative paths may be either forward (/) or backslash (\). You may mix the types of slash characters in the same path.

•Universal naming convention (UNC) path names are permitted.

When you provide an ISR table name for the sort-order parameter, the following statements apply:

•PSQL supports the table names listed in this documentation under International Sort Rules.

•The ISR table name is not the name of a file, but rather a string recognized by PSQL as one of the ISRs that it supports.

•PSQL supports selected Unicode collations based on International Components for Unicode (ICU). Simply use the ICU collation name in place of the ISR table name. The available collations are described under Collation Support Using an ICU Unicode Collation.

This ACS example sets a collating sequence using the upper.alt file supplied with PSQL. The table t1 is created with three text columns and three columns not text. A SELECT statement executes against PSQL system tables to return the ID, type, size, and attributes of the columns in t1. The result shows that the three text columns have an attribute of UPPER.

CREATE TABLE t1 (c1 INT, c2 CHAR(10), c3 BINARY(10), c4 VARCHAR(10), c5 LONGVARBINARY, c6 LONGVARCHAR)

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

The following ACS example continues with the use of table t1. An ALTER TABLE statement changes the text column c2 from a CHAR to an INTEGER. The result of the SELECT statement shows that now only two columns are affected by the default collating.

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

The following ACS example uses an ALTER TABLE statement to change column c1 in table t1 from an INTEGER to a CHAR. The result of the SELECT statement shows that three columns are affected by the default collating.

SELECT * FROM x$attrib WHERE xa$id in (SELECT xe$id FROM x$field WHERE xe$file = (SELECT xf$id FROM x$file WHERE xf$name = 't1'))

The following ISR example creates a table with a VARCHAR column, assumes the default Windows encoding CP1252, and uses the ISR collation MSFT_ENUS01252_0.

create table isrtest (ord INT, value VARCHAR(19) COLLATE 'MSFT_ENUS01252_0' not null, primary key(value));

The following ICU example creates a table with a VARCHAR column, assumes the default Linux encoding UTF-8, and uses the ICU collation u54-msft_enus_0.

create table isrtest (ord INT, value VARCHAR(19) COLLATE 'u54-msft_enus_0' not null, primary key(value));

The following ICU example creates a table with an NVARCHAR column using the ICU collation u54-msft_enus_0.

create table isrtest (ord INT, value NVARCHAR(19) COLLATE 'u54-msft_enus_0' not null, primary key(value));