Assigning collation sequences from SQL

Updated: April 19, 2023

Every column of every table has a default collation type. If a column requires a type type other than BINARY, you can define the preferred collation type by specifying a COLLATE clause as part of the CREATE TABLE column definition.

Whenever QDB compares two text values, it uses a collation sequence to determine the results of the comparison according to the following rules:

The expression x BETWEEN y and z is equivalent to x >= y AND x <= z. The expression x IN (SELECT y ...) is handled in the same way as the expression x = y for the purpose of determining the collation sequence to use. The sequence used for expressions of the form x IN (y, z ...) is the default collation type of x if x is a column, or BINARY otherwise.

Within a SELECT statement, an ORDER BY clause may contain a COLLATE clause that explicitly names a collation sequence to use as the sorting operation. Otherwise, if the expression sorted by the ORDER BY clause is a column, then the column's default collation type determines the sorting order. If the expression is not a column, then the BINARY collation sequence is used.