INSERT statement [YCQL]

Synopsis

Use the INSERT statement to add a row to a specified table.

Syntax

Diagram

using_expression

ebnf

using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

ttl_or_timestamp_expression

ebnf

ttl_or_timestamp_expression = 'TTL' ttl_expression | 'TIMESTAMP' timestamp_expression;

Grammar

ebnf

insert ::= INSERT INTO table_name ( column_name [ , ... ] ) VALUES (
           expression [ , ... ] )
           [ IF { [ NOT ] EXISTS | if_expression } ]
           [ USING using_expression ]
           [ RETURNS STATUS AS ROW ]

Where

table_name and column are identifiers (table_name may be qualified with a keyspace name).
value can be any expression although Apache Cassandra requires that values must be literals.
Restrictions for if_expression and ttl_expression are covered in the Semantics section.
See Expressions for more information on syntax rules.

Semantics

An error is raised if the specified table_name does not exist.
The columns list must include all primary key columns.
The USING TIMESTAMP clause indicates you would like to perform the INSERT as if it was done at the timestamp provided by the user. The timestamp is the number of microseconds since epoch.
By default INSERT has upsert semantics, that is, if the row already exists, it behaves like an UPDATE. If pure INSERT semantics is desired then the IF NOT EXISTS clause can be used to make sure an existing row is not overwritten by the INSERT.
Note: You should either use the USING TIMESTAMP clause in all of your statements or none of them. Using a mix of statements where some have USING TIMESTAMP and others do not will lead to very confusing results.
Inserting rows with TTL is not supported on tables with transactions enabled.
INSERT is always done at QUORUM consistency level irrespective of setting.

`VALUES` clause

The values list must have the same length as the columns list.
Each value must be convertible to its corresponding (by position) column type.
Each value literal can be an expression that evaluates to a simple value.

`IF` clause

The if_expression can only apply to non-key columns (regular columns).
The if_expression can contain any logical and boolean operators.

`USING` clause

ttl_expression must be an integer value (or a bind variable marker for prepared statements).
timestamp_expression must be an integer value (or a bind variable marker for prepared statements).

Examples

Insert a row into a table

sql

ycqlsh:example> CREATE TABLE employees(department_id INT,
                                      employee_id INT,
                                      name TEXT,
                                      PRIMARY KEY(department_id, employee_id));

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 1, 'John');

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 2, 'Jane');

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane

Conditional insert using the `IF` clause

Example 1

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Joe') IF name = null;

output

 [applied]
-----------
      True

Example 2

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Jack') IF NOT EXISTS;

output

 [applied]
-----------
     False

Example 3

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             1 |           1 | John
             1 |           2 | Jane

Insert a row with expiration time using the `USING TTL` clause

You can do this as follows:

sql

ycqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 2, 'Jack') USING TTL 10;

Now query the employees table.

sql

ycqlsh:example> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             2 |           2 | Jack
             1 |           1 | John
             1 |           2 | Jane

Again query the employees table after 11 seconds or more.

sql

ycqlsh:example> SELECT * FROM employees; -- 11 seconds after the insert.

output

 department_id | employee_id | name
---------------+-------------+------
             2 |           1 |  Joe
             1 |           1 | John
             1 |           2 | Jane

Insert a row with `USING TIMESTAMP` clause

Insert a row with a low timestamp

sql

ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jeff') USING TIMESTAMP 1000;

Now query the employees table.

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+------
             1 |           1 | John
             1 |           2 | Jane
             1 |           3 | Jeff
             2 |           1 |  Joe

(4 rows)

Overwrite the row with a higher timestamp

sql

ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jerry') USING TIMESTAMP 2000;

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+-------
             1 |           1 |  John
             1 |           2 |  Jane
             1 |           3 | Jerry
             2 |           1 |   Joe

(4 rows)

Try to overwrite the row with a lower timestamp

sql

ycqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'James') USING TIMESTAMP 1500;

sql

ycqlsh:foo> SELECT * FROM employees;

output

 department_id | employee_id | name
---------------+-------------+-------
             1 |           1 |  John
             1 |           2 |  Jane
             1 |           3 | Jerry
             2 |           1 |   Joe

(4 rows)

RETURNS STATUS AS ROW

When executing a batch in YCQL, the protocol returns only one error or return status. The RETURNS STATUS AS ROW feature addresses this limitation and adds a status row for each statement.

See examples in batch docs.

Synopsis

Syntax

Diagram

using_expression

ttl_or_timestamp_expression

Grammar

Semantics

VALUES clause

IF clause

USING clause

Examples

Insert a row into a table

Conditional insert using the IF clause

Insert a row with expiration time using the USING TTL clause

Insert a row with USING TIMESTAMP clause

Insert a row with a low timestamp

Overwrite the row with a higher timestamp

Try to overwrite the row with a lower timestamp

RETURNS STATUS AS ROW

See also

`VALUES` clause

`IF` clause

`USING` clause

Conditional insert using the `IF` clause

Insert a row with expiration time using the `USING TTL` clause

Insert a row with `USING TIMESTAMP` clause