INSERT

AttentionThis page documents an earlier version. Go to the latest (v2.1)version.

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 thetimestamp 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 ofthem. Using a mix of statements where some have USING TIMESTAMP and others do not will lead tovery 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

You can do this as shown below.

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

You can do this as shown below.

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

 [applied]

  True

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

 [applied]

 False

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;

cqlsh:example> SELECT * FROM employees;

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

Approximately 11 seconds after the insert.

cqlsh:example> SELECT * FROM employees; 

 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.

You can do this as shown below.

cqlsh:foo> INSERT INTO employees(department_id, employee_id, name) VALUES (1, 3, 'Jeff') USING TIMESTAMP 1000;
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.

You can do this as shown below.

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.

You can do this as shown below.

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)

See Also

CREATE TABLEDELETESELECTUPDATEExpressionOther CQL Statements