Names and identifiers - Amazon Redshift

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 lowercase in the database. In query results, column names are returned as lowercase by default. To return column names in uppercase, set the describe_field_name_in_uppercase configuration parameter to true.

Standard identifiers

Standard SQL identifiers adhere to a set of rules and must:

  • Begin with 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 lowercase. 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:

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

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)
select "this ""is it""" from "group"; this "is it" -------------- (0 rows)

The following fully qualified table.column syntax also returns the same result:

select "group"."this ""is it""" from "group"; this "is it" -------------- (0 rows)

The following CREATE TABLE command creates a table with a slash in a column name:

create table if not exists city_slash_id( "city/id" integer not null, state char(2) not null);