INSERT

Inserts an entire row or upserts data into existing rows.

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 values are set to null. Returns no results unless IF NOT EXISTS is used.

Restriction:

Synopsis

INSERT INTO [keyspace_name.]table_name
  [ column_list VALUES column_values ]
  [ IF NOT EXISTS ] 
  [ USING [ TTL seconds ] [ [ AND ] TIMESTAMP epoch_in_microseconds ] ] ;
Note: INSERT also supports JSON syntax to provide manual testing and troubleshooting from the command line, see Inserting JSON formatted values.
Note: To modify a base table that has a materialized view (MV) using an INSERT or UPDATE command if access permissions are enabled, a user must be granted MODIFY or ALL PERMISSIONS on the base table.
Table 1. Legend
Syntax conventions Description
UPPERCASE Literal keyword.
Lowercase Not literal.
Italics Variable value. Replace with a user-defined value.
[] Optional. Square brackets ( [] ) surround optional command arguments. Do not type the square brackets.
( ) Group. Parentheses ( ( ) ) identify a group to choose from. Do not type the parentheses.
| Or. A vertical bar ( | ) separates alternative elements. Type any one of the elements. Do not type the vertical bar.
... Repeatable. An ellipsis ( ... ) indicates that you can repeat the syntax element as often as required.
'Literal string' Single quotation ( ' ) marks must surround literal strings in CQL statements. Use single quotation marks to preserve upper case.
{ key : value } Map collection. Braces ( { } ) enclose map collections or key value pairs. A colon separates the key and the value.
<datatype1,datatype2> Set, list, map, or tuple. Angle brackets ( < > ) enclose data types in a set, list, map, or tuple. Separate the data types with a comma.
cql_statement; End CQL statement. A semicolon ( ; ) terminates all CQL statements.
[--] Separate the command line options from the command arguments with two hyphens ( -- ). This syntax is useful when arguments might be mistaken for command line options.
' <schema> ... </schema> ' Search CQL only: Single quotation marks ( ' ) surround an entire XML schema declaration.
@xml_entity='xml_entity_type' 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.

Warning: 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=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 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

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 categories for a cyclist.
INSERT INTO cycling.cyclist_categories (id,lastname,categories)
  VALUES(
    '6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47', 
    'KRUIJSWIJK', 
    {'GC', 'Time-trial', 'Sprint'});
Insert a map named teams that lists two recent team memberships for the user VOS.
INSERT INTO cycling.cyclist_teams (id,lastname,teams)
  VALUES(
    5b6962dd-3f90-4c93-8f61-eabfa4a803e2, 
    '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 returns true (if there is no row with this primary key), standard output displays a table like the following:
 [applied]
-----------
      True
If, however, the row does already exist, the command fails, and standard out displays a table with the value false in the [applied] column, and the values that were not inserted, as in the following example:
 [applied] | id                                   | firstname | lastname
-----------+--------------------------------------+-----------+----------
     False | c4b65263-fe58-4846-83e8-f0e1c13d518f |  Rissella |    RATTO
Note: Using IF NOT EXISTS incurs a performance hit associated with using Paxos internally. For information about Paxos, see Linearizable consistency.