INSERT
Inserts an entire row or upserts data into an existing row; statement must include the full Primary_key. Requires a value for each component of the primary key, but not for any other columns. Missing columns are unset by default and do not create tombstones in the database. Returns no results unless IF NOT EXISTS is used.
Restriction:
-
Insert does not support counter columns, use UPDATE instead.
-
A PRIMARY KEY consists of a the partition key followed by the clustering columns.
Synopsis
INSERT [ JSON ] INTO [<keyspace_name>.]<table_name> [ <column_list> VALUES <column_values> ] [ IF NOT EXISTS ] [ USING [ TTL <seconds> ] [ [ AND ] TIMESTAMP <epoch_in_microseconds> ] ] ;
INSERT also supports JSON syntax to provide manual testing and troubleshooting from the command line, see Inserting JSON formatted values. |
To modify a base table that has a materialized view (MV) using an |
Syntax legend
Syntax conventions | Description |
---|---|
UPPERCASE |
Literal keyword. |
Lowercase |
Not literal. |
|
Variable value. Replace with a user-defined value. |
|
Optional.
Square brackets ( |
|
Group.
Parentheses ( |
|
Or.
A vertical bar ( |
|
Repeatable.
An ellipsis ( |
|
Single quotation ( |
|
Map collection.
Braces ( |
Set, list, map, or tuple.
Angle brackets ( |
|
|
End CQL statement.
A semicolon ( |
|
Separate the command line options from the command arguments with two hyphens ( |
|
Search CQL only: Single quotation marks ( |
|
Search CQL only: Identify the entity and literal value to overwrite the XML element in the schema and solrConfig files. |
- column_list
-
Comma-separated list of columns. All PRIMARY KEY fields are required. Nulls are inserted into any static columns that are excluded.
- column_values
-
For each column, enter the corresponding list of values. Use the same order as the column_list.
Enter data using a literal or the following syntax for collections:
-
Set: Enter values between curly braces:
{ literal [, ...] }
. -
List: Enter values between square brackets:
[literal [, ...]]
. -
Map: Enter values between curly braces:
{ key : value [, ...] }
.
-
- TTL <seconds>
-
Set TTL in seconds. After TTL expires, inserted data is automatically marked as deleted (with a tombstone). The TTL settings applies only to the inserted data, not the entire column. Any subsequent updates to the column resets the TTL. By default, values never expire.
You can set a default TTL for an entire table by setting the table’s default_time_to_live property. Setting TTL on a column using the INSERT or UPDATE command overrides the table TTL.
The database storage engine can only encode TTL timestamps through
January 19 2038 03:14:07 UTC
due to the Year 2038 problem. The TTL date overflow policy determines whether requests with expiration timestamps later than the maximum date are rejected or inserted. See -Dcassandra.expiration_date_overflow_policy. - IF NOT EXISTS
-
Inserts a new row of data if no rows match the PRIMARY KEY values.
- TIMESTAMP <epoch_in_microseconds>
-
Marks inserted data (write time) with
TIMESTAMP
. Enter the time since epoch (January 1, 1970) in microseconds. By default, the actual time of write is used.Restriction: INSERT does not support IF NOT EXISTS and USING TIMESTAMP in the same statement.
Examples
Specifying time-to-live (TTL) and timestamp
Insert a cyclist name using both a TTL and timestamp.
INSERT INTO cycling.cyclist_name (
id, lastname, firstname
) VALUES (
6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
)
USING TTL 86400
AND TIMESTAMP 123456789;
-
Time-to-live (TTL) in seconds
-
Timestamp in microseconds since epoch
Specifying time-to-live (TTL) and timestamp in collections.
Insert a cyclist name using both a TTL and timestamp for a sponsorship
set collection.
INSERT INTO cycling.cyclist_sponsors_expire (
cyclist_name, sponsorship
) VALUES (
'PRIETO, Marcela',
{ 'Castrelli', 'Alfa Romeo' }
)
USING TIMESTAMP 100
AND TTL 10000;
-
Time-to-live (TTL) in seconds
-
Timestamp in microseconds since epoch
Inserting values into a collection (set and map)
To insert data into a collection, enclose values in curly brackets.
Set values must be unique.
Insert a list of teams as a set
for the cyclist <VOS>.
The set is defined in the table as teams set<text>
.
INSERT INTO cycling.cyclist_career_teams (
id, lastname, teams
) VALUES (
5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
'VOS',
{
'Rabobank-Liv Woman Cycling Team',
'Rabobank-Liv Giant',
'Rabobank Women Team',
'Nederland bloeit'
}
);
Insert data into a map
named teams
that lists two recent team memberships for the cyclist <VOS>.
The map is defined in the table as teams map<int, text>
.
INSERT INTO cycling.cyclist_teams (
id, firstname, lastname, teams
) VALUES (
5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
'Marianne',
'VOS',
{
2015 : 'Rabobank-Liv Woman Cycling Team',
2014 : 'Rabobank-Liv Woman Cycling Team'
}
);
The size of one item in a collection is limited to 64K.
To insert data into a collection column of a user-defined type, enclose components of the type in parentheses within the curly brackets, as shown in Using a user-defined type.
Inserting a row only if it does not already exist
Add IF NOT EXISTS to the command to ensure that the operation is not performed if a row with the same primary key already exists:
INSERT INTO cycling.cyclist_name (
id, lastname, firstname
) VALUES (
c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
)
IF NOT EXISTS;
Without IF NOT EXISTS, the command proceeds with no standard output.
If IF NOT EXISTS is included and there is no row with the same primary key, the command is performed and returns true
in the [applied]
column of the results.
For example:
[applied]
-----------
True
With IF NOT EXISTS, if the row already exists, the command returns false
in the [applied]
column and returns the values for the existing row.
For example:
[applied] | id | firstname | lastname
-----------+--------------------------------------+-----------+----------
False | c4b65263-fe58-4846-83e8-f0e1c13d518f | Rissella | RATTO
Using IF NOT EXISTS incurs a performance hit associated with using Paxos internally. Linearizable consistency. |