SQL Reserved Words

[Deleted User]

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.

Example:

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)

Have Fun!

Pieter_Sheth-Vo

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:

SELECT 
'ALL' AS ALL,
'AND' AS AND,
'ANY' AS ANY,
'ARRAY' AS ARRAY,
'AS' AS AS,
'ASC' AS ASC,
'AUTHORIZATION' AS AUTHORIZATION,
'BETWEEN' AS BETWEEN,
'BIGINT' AS BIGINT,
'BINARY' AS BINARY,
'BIT' AS BIT,
'BOOLEAN' AS BOOLEAN,
'BOTH' AS BOTH,
'CASE' AS CASE,
'CAST' AS CAST,
'CHAR' AS CHAR,
'CHAR_LENGTH' AS CHAR_LENGTH,
'CHARACTER_LENGT' AS CHARACTER_LENGT,
'CHECK' AS CHECK,
'COLLATE' AS COLLATE,
'COLUMN' AS COLUMN,
'CONSTRAINT' AS CONSTRAINT,
'CORRELATION' AS CORRELATION,
'CREATE' AS CREATE,
'CROSS' AS CROSS,
'CURRENT_DATABAS' AS CURRENT_DATABAS,
'CURRENT_DATE' AS CURRENT_DATE,
'CURRENT_SCHEMA' AS CURRENT_SCHEMA,
'CURRENT_TIME' AS CURRENT_TIME,
'CURRENT_TIMESTA' AS CURRENT_TIMESTA,
'CURRENT_USER' AS CURRENT_USER,
'DATEDIFF' AS DATEDIFF,
'DATETIME' AS DATETIME,
'DECIMAL' AS DECIMAL,
'DECODE' AS DECODE,
'DEFAULT' AS DEFAULT,
'DEFERRABLE' AS DEFERRABLE,
'DESC' AS DESC,
'DISTINCT' AS DISTINCT,
'ELSE' AS ELSE,
'ENCODED' AS ENCODED,
'END' AS END,
'EXCEPT' AS EXCEPT,
'EXISTS' AS EXISTS,
'EXTRACT' AS EXTRACT,
'FALSE' AS FALSE,
'FLOAT' AS FLOAT,
'FOR' AS FOR,
'FOREIGN' AS FOREIGN,
'FROM' AS FROM,
'FULL' AS FULL,
'GRANT' AS GRANT,
'GROUP' AS GROUP,
'HAVING' AS HAVING,
'ILIKE' AS ILIKE,
'ILIKEB' AS ILIKEB,
'IN' AS IN,
'INITIALLY' AS INITIALLY,
'INNER' AS INNER,
'INOUT' AS INOUT,
'INT' AS INT,
'INTEGER' AS INTEGER,
'INTERSECT' AS INTERSECT,
'INTERVAL' AS INTERVAL,
'INTERVALYM' AS INTERVALYM,
'INTO' AS INTO,
'IS' AS IS,
'ISNULL' AS ISNULL,
'JOIN' AS JOIN,
'KSAFE' AS KSAFE,
'LEADING' AS LEADING,
'LEFT' AS LEFT,
'LIKE' AS LIKE,
'LIKEB' AS LIKEB,
'LIMIT' AS LIMIT,
'LOCALTIME' AS LOCALTIME,
'LOCALTIMESTAMP' AS LOCALTIMESTAMP,
'MATCH' AS MATCH,
'MINUS' AS MINUS,
'MONEY' AS MONEY,
'NATURAL' AS NATURAL,
'NCHAR' AS NCHAR,
'NEW' AS NEW,
'NONE' AS NONE,
'NOT' AS NOT,
'NOTNULL' AS NOTNULL,
'NULL' AS NULL,
'NULLSEQUAL' AS NULLSEQUAL,
'NUMBER' AS NUMBER,
'NUMERIC' AS NUMERIC,
'OFFSET' AS OFFSET,
'OLD' AS OLD,
'ON' AS ON,
'ONLY' AS ONLY,
'OR' AS OR,
'ORDER' AS ORDER,
'OUT' AS OUT,
'OUTER' AS OUTER,
'OVER' AS OVER,
'OVERLAPS' AS OVERLAPS,
'OVERLAY' AS OVERLAY,
'PINNED' AS PINNED,
'POSITION' AS POSITION,
'PRECISION' AS PRECISION,
'PRIMARY' AS PRIMARY,
'REAL' AS REAL,
'REFERENCES' AS REFERENCES,
'RIGHT' AS RIGHT,
'ROW' AS ROW,
'SCHEMA' AS SCHEMA,
'SELECT' AS SELECT,
'SESSION_USER' AS SESSION_USER,
'SIMILAR' AS SIMILAR,
'SMALLDATETIME' AS SMALLDATETIME,
'SMALLINT' AS SMALLINT,
'SOME' AS SOME,
'SUBSTRING' AS SUBSTRING,
'SYSDATE' AS SYSDATE,
'TABLE' AS TABLE,
'TEXT' AS TEXT,
'THEN' AS THEN,
'TIME' AS TIME,
'TIMESERIES' AS TIMESERIES,
'TIMESTAMP' AS TIMESTAMP,
'TIMESTAMPADD' AS TIMESTAMPADD,
'TIMESTAMPDIFF' AS TIMESTAMPDIFF,
'TIMESTAMPTZ' AS TIMESTAMPTZ,
'TIMETZ' AS TIMETZ,
'TIMEZONE' AS TIMEZONE,
'TINYINT' AS TINYINT,
'TO' AS TO,
'TRAILING' AS TRAILING,
'TREAT' AS TREAT,
'TRIM' AS TRIM,
'TRUE' AS TRUE,
'UNBOUNDED' AS UNBOUNDED,
'UNION' AS UNION,
'UNIQUE' AS UNIQUE,
'USER' AS USER,
'USING' AS USING,
'UUID' AS UUID,
'VARBINARY' AS VARBINARY,
'VARCHAR' AS VARCHAR,
'VARCHAR2' AS VARCHAR2,
'WHEN' AS WHEN,
'WHERE' AS WHERE,
'WINDOW' AS WINDOW,
'WITH' AS WITH,
'WITHIN' AS WITHIN
;

returns a table with these reserved words as column names without complaint.

marcothesane

Not quite. Trying to create a table with those column names - I do get an error in the first wrong column name:

152 -- out ERROR 4856:  Syntax error at or near "ALL" at character 24
153 -- out LINE 2: ALL int,
154 -- out         ^                                                                                                                                                                                   

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.

  1 CREATE TABLE resword (
  2   ALL int,                                                                                                                                                                                         
  3   AND int,
  4   ANY int,
  5   ARRAY int,
  6   AS int,
  7   ASC int,
  8   AUTHORIZATION int,
  9   BETWEEN int,
 10   BIGINT int,
 11   BINARY int,
 12   BIT int,
 13   BOOLEAN int,
 14   BOTH int,
 15   CASE int,
 16   CAST int,
 17   CHAR int,
 18   CHAR_LENGTH int,
 19   CHARACTER_LENGT int,
 20   CHECK int,
 21   COLLATE int,
 22   COLUMN int,
 23   CONSTRAINT int,
 24   CORRELATION int,
 25   CREATE int,
 26   CROSS int,
 27   CURRENT_DATABAS int,
 28   CURRENT_DATE int,
 29   CURRENT_SCHEMA int,
 30   CURRENT_TIME int,
 31   CURRENT_TIMESTA int,
 32   CURRENT_USER int,
 33   DATEDIFF int,
 34   DATETIME int,
 35   DECIMAL int,
 36   DECODE int,
 37   DEFAULT int,
 38   DEFERRABLE int,
 39   DESC int,
 40   DISTINCT int,
 41   ELSE int,
 42   ENCODED int,
 43   END int,
 44   EXCEPT int,
 45   EXISTS int,
 46   EXTRACT int,
 47   FALSE int,
 48   FLOAT int,
 49   FOR int,
 50   FOREIGN int,
 51   FROM int,
 52   FULL int,
 53   GRANT int,
 54   GROUP int,
 55   HAVING int,
 56   ILIKE int,
 57   ILIKEB int,
 58   IN int,
 59   INITIALLY int,
 60   INNER int,
 61   INOUT int,
 62   INT int,
 63   INTEGER int,
 64   INTERSECT int,
 65   INTERVAL int,
 66   INTERVALYM int,
 67   INTO int,
 68   IS int,
 69   ISNULL int,
 70   JOIN int,
 71   KSAFE int,
 72   LEADING int,
 73   LEFT int,
 74   LIKE int,
 75   LIKEB int,
 76   LIMIT int,
 77   LOCALTIME int,
 78   LOCALTIMESTAMP int,
 79   MATCH int,
 80   MINUS int,
 81   MONEY int,
 82   NATURAL int,
 83   NCHAR int,
 84   NEW int,
 85   NONE int,
 86   NOT int,
 87   NOTNULL int,
 88   NULL int,
 89   NULLSEQUAL int,
 90   NUMBER int,
 91   NUMERIC int,
 92   OFFSET int,
 93   OLD int,
 94   ON int,
 95   ONLY int,
 96   OR int,
 97   ORDER int,
 98   OUT int,
 99   OUTER int,
100   OVER int,
101   OVERLAPS int,
102   OVERLAY int,
103   PINNED int,
104   POSITION int,
105   PRECISION int,
106   PRIMARY int,
107   REAL int,
108   REFERENCES int,
109   RIGHT int,
110   ROW int,
111   SCHEMA int,
112   SELECT int,
113   SESSION_USER int,
114   SIMILAR int,
115   SMALLDATETIME int,
116   SMALLINT int,
117   SOME int,
118   SUBSTRING int,
119   SYSDATE int,
120   TABLE int,
121   TEXT int,
122   THEN int,
123   TIME int,
124   TIMESERIES int,
125   TIMESTAMP int,
126   TIMESTAMPADD int,
127   TIMESTAMPDIFF int,
128   TIMESTAMPTZ int,
129   TIMETZ int,
130   TIMEZONE int,
131   TINYINT int,
132   TO int,
133   TRAILING int,
134   TREAT int,
135   TRIM int,
136   TRUE int,
137   UNBOUNDED int,
138   UNION int,
139   UNIQUE int,
140   USER int,
141   USING int,
142   UUID int,
143   VARBINARY int,
144   VARCHAR int,
145   VARCHAR2 int,
146   WHEN int,
147   WHERE int,
148   WINDOW int,
149   WITH int,
150   WITHIN int
151 )
152 -- out ERROR 4856:  Syntax error at or near "ALL" at character 24
153 -- out LINE 2: ALL int,
154 -- out         ^