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
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 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 haveUSING 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
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]
[applied]
True
Example 2
cqlsh:example> INSERT INTO employees(department_id, employee_id, name) VALUES (2, 1, 'Jack') IF NOT EXISTS;
[applied]
[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)
See also
当前内容版权归 YugabyteDB 或其关联方所有,如需对内容或内容相关联开源项目进行关注与资助,请访问 YugabyteDB .