Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

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 Identifiers

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

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.

Examples

This table shows examples of delimited identifiers, the resulting output, and a discussion:

Syntax Result 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":

Copy
create table "group" ( "This ""IS IT""" char(10));

The following queries return the same result:

Copy
select "This ""IS IT""" from "group"; this "is it" -------------- (0 rows)

Copy
select "this ""is it""" from "group"; this "is it" -------------- (0 rows)
The following fully qualified table.column syntax also returns the same result:
Copy
select "group"."this ""is it""" from "group"; this "is it" -------------- (0 rows)