Understanding the CQL command syntax

Identifiers created using CQL are case-insensitive unless enclosed in double quotation marks. If you enter names for these objects using any uppercase letters, the database stores the names in lowercase. You can force the case using double quotation marks. For example:

CREATE TABLE test (
  Foo int PRIMARY KEY,
  "Bar" int
);

The following table shows partial queries that work and do not work to return results from the test table:

SELECT "foo" FROM ... works because the database stores foo in lowercase.

When using legacy tables, case-sensitivity rules in earlier versions of CQL apply.

CQL keywords are case-insensitive. For example, SELECT and select are equivalent. This document shows keywords in uppercase.

To escape characters, see Escaping characters.

Only alpha-numeric characters and underscores are allowed in keyspace and table names. Keyspace and table names must begin with an alphabetic charcter. All other element names, such as columns or types support any characters.

To specify a name that contains a special character, like period (.) or hyphen (-), enclose the name in double quotes.

Column names that contain characters that CQL cannot parse need to be enclosed in double quotation marks in CQL.

An alternative is to use dollar-quoted strings. Dollar-quoted string constants can be used to create functions, insert data, and select data when complex quoting is needed. Use double dollar signs to enclose the desired string.

INSERT INTO cycling.calendar (race_id, race_start_date, race_end_date, race_name) VALUES 
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);

Valid literal consist of these kinds of values:

• blob

  hexadecimal defined as 0[xX](hex)+

• boolean

  true or false, case-insensitive, not enclosed in quotation marks

• numeric constant

  A numeric constant can consist of integers 0-9 and a minus sign prefix. A numeric constant can also be float. A float can be a series of one or more decimal digits, followed by a period, ., and one or more decimal digits. There is no optional + sign. The forms .42 and 42 are unacceptable. You can use leading or trailing zeros before and after decimal points. For example, 0.42 and 42.0. A float constant, expressed in E notation, consists of the characters in this regular expression:

  '-'?[0-9]+('.'[0-9]*)?([eE][+-]?[0-9+])?

  NaN and Infinity are floats.

• identifier

  Names of tables, columns, types, and other objects are identifiers. Since keyspace and table names are used in system file names, they must start with a letter or number and can only contain alphanumeric characters and underscores. All other identifiers, such as column and user-defined function names can contain any character. To specify an identifier that contains a special character enclose the name in quotes.

• integer

  An optional minus sign, -, followed by one or more digits.

• string literal

  Characters enclosed in single quotation marks. To use a single quotation mark itself in a string literal, escape it using a single quotation mark. For example, use ''to make dog possessive: dog''s.

• uuid

  32 hex digits, 0-9 or a-f, which are case-insensitive, separated by dashes, -, after the 8th, 12th, 16th, and 20th digits. For example: 01234567-0123-0123-0123-0123456789ab

• timeuuid

  Uses the time in 100 nanosecond intervals since 00:00:00.00 UTC (60 bits), a clock sequence number for prevention of duplicates (14 bits), plus the IEEE 801 MAC address (48 bits) to generate a unique identifier. For example: d2177dd0-eaa2-11de-a572-001b779c76e3

• whitespace

  Separates terms and used inside string literals, but otherwise CQL ignores whitespace.

CREATE TABLE test(
  id varchar PRIMARY KEY,
  value_double double,
  value_float float
);

INSERT INTO test (id, value_float, value_double)
  VALUES ('test1', -2.6034345E+38, -2.6034345E+38);

SELECT * FROM test;

 id    | value_double | value_float
-------+--------------+-------------
 test1 |  -2.6034e+38 | -2.6034e+38

This table lists keywords and whether the words are reserved. A reserved keyword cannot be used as an identifier unless you enclose the word in double quotation marks. Non-reserved keywords have a specific meaning in certain context but can be used as an identifier outside this context.

Reserved keywords

CREATE TABLE test."add" (a int PRIMARY KEY);

Non-reserved keywords

Non-reserved keywords have a specific meaning in certain context but can be used as an identifier outside this context.