Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
SQL Reserved Words
Jim Knicely authored this tip.
Keywords are words that have a specific meaning in the SQL language. Every SQL statement contains one or more keywords. Many keywords are also reserved words. Vertica recommends that you not use reserved words as names for objects, or as identifiers. Including reserved words can make your SQL statements confusing.
You can query the V_CATALOG.STANDARD_KEYWORDS system table to find the list of Vertica SQL reserved words.
dbadmin=> SELECT *FROM v_catalog.standard_keywords WHERE reserved = 'R' ORDER BY standard_version DESC LIMIT 1; keyword | standard_version | reserved ---------+------------------+---------- ABS | 2011 | R (1 row)
Notice how the number of reserved words increases by ANSI SQL version:
dbadmin=> SELECT standard_version, COUNT(*) reserved_word_cnt FROM v_catalog.standard_keywords WHERE reserved = 'R' GROUP BY standard_version ORDER BY 1; standard_version | reserved_word_cnt ------------------+------------------- 1992 | 227 2003 | 288 2008 | 298 2011 | 357 (4 rows)
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Is this just a recommendation ? The docs for Vertica 9.3 say "Reserved words that are used as names for objects or identifiers must be enclosed in double-quotes." but for 11.3 it says just "Vertica recommends that you not use reserved words as names for objects, or as identifiers"
For context, sometimes when importing client data files, they may have column names that are in the list of SQL reserved words, and would be nice not to have to double quote the names and deal with case sensitivity.
The following query seems to work ok:
returns a table with these reserved words as column names without complaint.
Not quite. Trying to create a table with those column names - I do get an error in the first wrong column name:
In addition, at any time the "tolerance level" with reserved words can change - and, moreover, think of portability - other DBMSs - and the ANSI standard - might not tolerate them.