Properly Identifying Identifiers

Vertica Identifiers (names) of objects such as schemas, tables, projections, 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 includes 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. It will be a pain to query later, since 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/latest/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Identifiers.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/QUOTE_IDENT.htm

Have fun!