Handle SQL identifiers

A SQL identifier is the name of a database object such as a table, column, user, group, user-defined object, and database. Netezza supports the SQL 1999 definition for naming identifiers, and they can be up to 128 bytes in length. There are two types of identifiers, regular and delimited.

Note: Account passwords, the names of files, and other values are not identifiers, and thus can support a reduced set of characters, including only 7-bit ASCII characters. The file names for external tables must be in UTF-8.

Regular identifier

A regular identifier is not case-sensitive; that is, if you create a database named SALES, you can call it by using any case combination of letters. For example, SALES, sales, SaLeS, and SALEs all match the database named SALES. The ANSI SQL standard specifies that systems convert all regular SQL identifiers to the corresponding uppercase characters, so the Netezza Performance Server system converts any regular identifier that you specify into uppercase characters when it is saved in the database, and also when the regular identifiers are used in query processing.

Regular identifiers can contain only letters (in any alphabet, not just the Latin alphabet), syllables (as in the Japanese Hiragana syllabary), ideographs, decimal digits, underscores, and dollar sign ($). Regular identifiers must begin with a letter; they cannot begin with a digit, underscore, or dollar sign. Regular identifiers also cannot be a SQL reserved word (as described in SQL reserved words and keywords). The encoding in the Netezza Performance Server catalog is in UTF-8; the encoding for any display depends on the client.

Delimited identifier

A delimited identifier is also a name of a database object, but it is enclosed in double quotation marks and has special considerations. A delimited identifier is case-sensitive, so a database named “Sales” is not the same database as one named SALES, for example. The Netezza Performance Server system does not convert delimited identifiers to the default system case. The Netezza Performance Server system also does not save the enclosing double quotation marks in the database.

Note: The system automatically truncates leading and trailing spaces in a delimited identifier. If you use leading and trailing spaces to format output, for example, make sure that you precede leading spaces and end trailing spaces with a non-space character to preserve the spacing in the identifier.

Within the double quotation marks, a delimited identifier can include the same letters, syllables, ideographs, decimal digits, and underscores as a regular identifier, but it can also include spaces, special characters such as hyphens and percent signs, and SQL reserved keywords. Except for underscores, which are not allowed, a delimited identifier can begin with any of these letters, digits, or symbols.

For example, the following query uses delimited identifiers for both column and table names:

SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%';

The string literal 'A%' is enclosed in single quotation marks.

You can change the system default lettercase behavior at system initialization by using the nzinitsystem -lowercase command. For more information about the nzinitsystem command, see the IBM® Netezza® System Administrator’s Guide.