SQL Identifiers

Identifiers are names of tables, columns, schemas, databases, and so on. Yellowbrick supports standard SQL identifiers and quoted identifiers.

SQL Identifiers

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

Quoted SQL Identifiers

Quoted identifiers (or delimited identifiers) are names enclosed in double quotes ("). To include a double quote in a quoted identifier, type two double quotes. Quoted identifiers are case-sensitive. By using quoted identifiers, you can create object names that contain explicit uppercase and lowercase letters, as well as special characters.

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)

See also Unicode Characters in Identifiers.

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