Skip to content

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.

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 ("). 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. However, you cannot use double quotes within object names.

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)

You can use reserved words as column aliases if you specify either the AS keyword or double quotes. For example, the following SELECT statements both work:

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

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

The following statement returns an error:

premdb=> select '123' advanced from sys.const;
ERROR:  syntax error at or near "advanced"
LINE 1: select '123' advanced from sys.const;

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

Parent topic:SQL Commands