Properly Identifying Identifiers

Jim_Knicely · March 2019

Vertica Identifiers (names) of objects such as schema, table, projection, column names, and so on, can be up to 128 bytes in length.

Unquoted SQL identifiers must begin with one of the following:

Non-Unicode letters: A–Z or a-z
Underscore (_)

Subsequent characters in an identifier can be any combination of the following:

Non-Unicode letters: A–Z or a-z
Underscore (_)
Digits(0–9)

If you have a requirement to break the above identifier naming rules, you can enclose the identifier in quotes.

Vertica induces a built-in function called QUOTE_IDENT that returns the given string, suitably quoted, to be used as an identifier in a SQL statement string. Quotes are added only if necessary.

Example:

dbadmin=> SELECT quote_ident('id'),
dbadmin->        quote_ident('id4'),
dbadmin->        quote_ident('4id'),
dbadmin->        quote_ident('"Vertica is better than Apple Pie!"');
quote_ident | quote_ident | quote_ident |               quote_ident
-------------+-------------+-------------+-----------------------------------------
id          | id4         | "4id"       | """Vertica is better than Apple Pie!"""
(1 row)

Note: Just because you can create a table called """Vertica is better than Apple Pie!""", it’s not the best idea as it’ll be a pain to query later as everyone always has to reference the table as created.

dbadmin=> CREATE TABLE """Vertica is better than Apple Pie!""" (
dbadmin(>  true_statement_but_not_the_best_identifier_name INT);
CREATE TABLE

dbadmin=> SELECT * FROM  """Vertica is better than Apple Pie!""";
true_statement_but_not_the_best_identifier_name
-------------------------------------------------
(0 rows)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Identifiers.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/QUOTE_IDENT.htm

Have fun!