INSERT
This page documents the preview version (v2.23). Preview includes features under active development and is for development and testing only. For production, use the stable version (v2024.1). To learn more, see Versioning.
Synopsis
Use the INSERT
statement to add 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_name [ , ... ] ) VALUES (
expression [ , ... ] )
[ IF { [ NOT ] EXISTS | if_expression } ]
[ USING using_expression ]
[ RETURNS STATUS AS ROW ]
Where
table_name
andcolumn
are identifiers (table_name
may be qualified with a keyspace name).value
can be any expression although Apache Cassandra requires thatvalue
s must be literals.- Restrictions for
if_expression
andttl_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
hasupsert
semantics, that is, if the row already exists, it behaves like anUPDATE
. If pureINSERT
semantics is desired then theIF NOT EXISTS
clause can be used to make sure an existing row is not overwritten by theINSERT
. - 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 haveUSING 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 atQUORUM
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.