Delimited identifiers

Identifiers are used in SQL to identify names of tables, columns, or other database objects, depending on the command they are used in.

Delimited identifier, or quoted identifier, is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So, "select" could be used to refer to a column or table named "select", whereas an unquoted select would be considered as a key word and would result in a parse error when used where a table or column name is expected.

Regular unquoted SQL identifiers and key words must begin with a letter (a-z,A-Z) or an underscore (_). Quoted identifiers can contain any character, except the character with code zero; the standard way to include a double quote is to write two double quotes. This allows constructing table or column names that would otherwise not be possible, such as ones containing spaces or ampersands.

In Raijin, quoted identifiers are also used in the INSERT command with JSON syntax to construct JSON field/value pairs.

Raijin supports an extended syntax for delimited identifiers and single-quoted string constants. The extended syntax allows using the backslash (C-style) escape sequences within the identifier or string constant. So, it is possible to use special symbols in names (for example, the new line \n, Unicode symbols, etc.); the details are in the Backslash (C-style) escaping section.

Identifier case sensitivity

The Raijin SQL parser uses the following rules for identifier case sensitivity:

  • Database and table names are not case-sensitive.

  • Column names are case-sensitive and are converted to lowercase by default unless they are enclosed in double quotes.

Because column names are case-sensitive, when your tables include mixed-case names, you must write your queries with column names enclosed in double quotes, e.g., select "SourceModuleName" from tbl. Mixed-case column/filed names are converted to lowercase if they are not enclosed in double-quotes. For example, the query result for select SourceModuleName from tbl will return "sourcemodulename":null even though the SourceModuleName column exists and all records have non-NULL values.

Identifier length limit

Raijin supports a maximum length of 255 characters. This limit applies to:

  • Aliases

  • Column names

  • Database names

  • Function names

  • Operators (sequences of symbols ~!@#^&|`?+-*/%<>=)

  • Option names (in WITH statements)

  • Parameter names (in SHOW <parameter> statements)

  • Partition names

  • Table names

  • Type names

  • View names