Identifiers

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

Unquoted Identifiers

Unquoted SQL identifiers must begin with one of the following:

• Letters (use A–Z or a-z only; behavior for Unicode is undefined)
• Underscore (_)

Subsequent characters in an identifier can be:

• Letters (as above)
• Unicode letters (letters with diacriticals or not in the Latin alphabet)
• Underscore (_)
• Digits(0–9)
• Dollar sign ($)

  Caution: The SQL standard does not support dollar sign in identifiers so usage can cause application portability problems.

Quoted Identifiers

Identifiers enclosed in double quote (") characters can contain any character. If you want to include a double quote, you need a pair of them; for example """". You can use names that would otherwise be invalid, such as names that include only numeric characters ("123") or contain space characters, punctuation marks, keywords, and so on; for example:

CREATE SEQUENCE "my sequence!";

Double quotes are required for non-alphanumerics and SQL keywords such as "1time", "Next week" and "Select".

Non-ASCII Characters

Vertica accepts non-ASCII UTF-8 Unicode characters for table names, column names, and other Identifiers, extending the cases in which upper/lower case distinctions are ignored (case-folded) to all alphabets, including Latin, Cyrillic, and Greek.

For example, the following CREATE TABLE statement uses the ß (German eszett) in the table name:

=> CREATE TABLE straße(x int, y int);
   CREATE TABLE

Identifiers Are Stored As Created

SQL identifiers, such as table and column names, are no longer converted to lowercase. They are stored as created, and references to them are resolved using case-insensitive compares. It is not necessary to double quote mixed-case identifiers. For example, the following statement creates table ALLCAPS.

=> CREATE TABLE ALLCAPS(c1 varchar(30));
=> INSERT INTO ALLCAPS values('upper case');

The following statements are variations of the same query and all return identical results:

=> SELECT * FROM ALLCAPS;
=> SELECT * FROM allcaps;
=> SELECT * FROM "allcaps";

All three commands return the same result:

     c1
------------
 upper case
(1 row)

Note that the system returns an error if you try to create table AllCaps:

=> CREATE TABLE AllCaps(c1 varchar(30));
   ROLLBACK:  table "AllCaps" already exists

See QUOTE_IDENT for additional information.