Names and Identifiers
Names identify database objects, including tables and columns, as well as users and passwords. The terms name and identifier can be used interchangeably. There are two types of identifiers, standard identifiers and quoted or delimited identifiers. Identifiers must consist of only UTF-8 printable characters. ASCII letters in standard and delimited identifiers are case-insensitive and are folded to lower case.
Standard SQL identifiers adhere to a set of rules and must:
Begin with an an ASCII single-byte alphabetic character or underscore character, or a UTF-8 multibyte character two to four bytes long.
Subsequent characters can be ASCII single-byte alphanumeric characters, underscores, or dollar signs, or UTF-8 multibyte characters two to four bytes long.
Be between 1 and 127 bytes in length, not including quotes for delimited identifiers.
Contain no quotation marks and no spaces.
Not be a reserved SQL key word.
Delimited identifiers (also known as quoted identifiers) begin and end with double quotation marks ("). If you use a delimited identifier, you must use the double quotation marks for every reference to that object. The identifier can contain any standard UTF-8 printable characters other than the double quote itself. Therefore, you can create column or table names that include otherwise illegal characters, such as spaces or the percent symbol.
ASCII letters in delimited identifiers are case-insensitive and are folded to lower case. To use a double quote in a string, you must precede it with another double quote character.
This table shows examples of delimited identifiers, the resulting output, and a discussion:
|"group"||group||GROUP is a reserved word, so usage of it within an identifier requires double quotes.|
|"""WHERE"""||"where"||WHERE is also a reserved word. To include quotation marks in the string, escape each double quote character with additional double quote characters.|
|"This name"||this name||Double quotes are required in order to preserve the space.|
|"This ""IS IT"""||this "is it"||The quotes surrounding IS IT must each be preceded by an extra quote in order to become part of the name.|
To create a table named group with a column named this "is it":
create table "group" ( "This ""IS IT""" char(10));
The following queries return the same result:
select "This ""IS IT""" from "group"; this "is it" -------------- (0 rows)
The following fully qualifiedCopy
select "this ""is it""" from "group"; this "is it" -------------- (0 rows)
table.columnsyntax also returns the same result:
select "group"."this ""is it""" from "group"; this "is it" -------------- (0 rows)