INSERT

Synopsis

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

Syntax

Diagram

INSERTINTOtable_name(,column_name)VALUES(,expression)IFNOTEXISTSif_expressionUSINGusing_expressionRETURNS STATUS AS ROW

using_expression

using_expression = ttl_or_timestamp_expression { 'AND' ttl_or_timestamp_expression };

ANDttl_or_timestamp_expression

ttl_or_timestamp_expression

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

TTLttl_expressionTIMESTAMPtimestamp_expression

Grammar

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

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

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

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

ycqlsh:example> SELECT * FROM employees;

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

Conditional insert using the IF clause

Example 1

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

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

Example 2

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

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

Example 3

ycqlsh: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 follows:

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

Now query the employees table.

ycqlsh: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.

ycqlsh: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

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

Now query the employees table.

ycqlsh: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

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

ycqlsh: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

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

ycqlsh:foo> SELECT * FROM employees;

 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.

See also