Home > APIs > YCQL >

INSERT

Synopsis

The INSERT statement adds a row to a specified table.

Syntax

Diagram

using_expression

using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

ttl_or_timestamp_expression

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

Grammar

insert ::= INSERT INTO table_name '(' column [ ',' column ... ] ')'
               VALUES '(' value [ ',' value ... ] ')'
               [ IF { [ NOT ] EXISTS | if_expression } ]
               [ USING using_expression ];

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 below.
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 we 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.
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.

`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

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

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

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

cqlsh:example> SELECT * FROM employees;

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

Conditional insert using the `IF` clause

Example 1

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

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

Example 2

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

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

Example 3

cqlsh:example> SELECT * FROM employees;

 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 shown below.

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

Now query the employees table.

cqlsh:example> SELECT * FROM employees;

 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.

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

 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.

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

Now query the employees table.

cqlsh:foo> SELECT * FROM employees;

 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.

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

cqlsh:foo> SELECT * FROM employees;

 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.

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

cqlsh:foo> SELECT * FROM employees;

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

(4 rows)