Product Documentation

Appearance

All platforms

SQL Identifiers ​

Identifiers are names of tables, columns, schemas, databases, and other database objects. Identifiers are frequently used in the commands described in this section. Yellowbrick supports standard SQL identifiers and quoted identifiers.

Unquoted SQL Identifiers ​

Note the following restrictions on unquoted SQL identifiers:

  • Must begin with a letter (a-z) or an underscore (_).
  • The pg_ prefix is disallowed and reserved for system objects.
  • Subsequent characters in an unquoted identifier can be letters, digits (0-9), or underscores.
  • Special characters such as #, $, and so on, are not allowed anywhere in an unquoted identifier.
  • Unquoted SQL identifiers are case-insensitive.

Important: Unquoted identifiers cannot match a SQL reserved word. If they do, you must either use double quotes or the AS keyword.

Quoted SQL Identifiers ​

Quoted identifiers (or delimited identifiers) are names enclosed in double quotes (").

  • Quoted identifiers are case-sensitive.
  • They allow the use of uppercase letters, lowercase letters, and special characters in names.
  • Double quotes cannot appear inside the identifier itself.

Note: Space characters are not allowed in database names.

For example:

premdb=# create table "COG$"(cost dec(5,3));
CREATE TABLE
premdb=# \d "COG$"
       Table "public.COG$"
 Column |     Type     | Modifiers
--------+--------------+-----------
 cost   | numeric(5,3) |

premdb=# create table "Pound#"(c1 int);
CREATE TABLE
premdb=# \d "Pound#"
   Table "public.Pound#"
 Column |  Type   | Modifiers
--------+---------+-----------
 c1     | integer |

Distribution: Hash (c1)

Using Keywords as Aliases ​

SQL keywords (both reserved and unreserved) cannot be used as bare column aliases. When used as aliases, they must either:

  • Be enclosed in double quotes, or
  • Be explicitly introduced with the AS keyword.

For example:

-- Works: quoted identifier
premdb=> select '123' "advanced" from sys.const;
 advanced
----------
 123
(1 row)

-- Works: alias with AS
premdb=> select '123' AS advanced from sys.const;
 advanced
----------
 123
(1 row)

-- Error: bare alias using a keyword
premdb=> select '123' advanced from sys.const;
ERROR:  syntax error at or near "advanced"
LINE 1: select '123' advanced from sys.const;

See reserved words for complete list of SQL keywords.

Maximum Length of Identifiers ​

In practice, the maximum length of an identifier is 128 bytes. Longer names may be used in queries, and in commands such as CREATE TABLE and CREATE VIEW, but these names are automatically truncated to 128 bytes.

When you write queries and command statements that use the full name of a long identifier, ybsql returns the truncated version of the name. You can see information messages when this truncation occurs by setting client_min_messages to NOTICE:

premdb=# set client_min_messages to NOTICE;
SET
premdb=# select * from t1... ;
NOTICE:  identifier "t1..." will be truncated to "t1..."
...