INSERT

If you insert data into an existing row, INSERT behaves as an upsert: the specified fields are overwritten and unspecified fields are left unchanged.

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.

Limitations

Insert does not support counter columns, use UPDATE instead.
A PRIMARY KEY consists of a 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> ] ] ;

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.

Legend
Syntax conventions	Description
UPPERCASE	Literal keyword.
Lowercase	Not literal.
`< >`	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.

Options

column_list

Comma-separated list of columns. You must include all primary key fields, but you do not have to include all columns.

column_values

If you set a column value to null, the database will create a tombstone.

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 [, ...] }.

INSERT also supports JSON syntax to provide manual testing and troubleshooting from the command line.

TTL <seconds>

Set time-to-live (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 (without TTL), values never expire.

You can set a default TTL for an entire table by setting the table’s default TTL property. Setting TTL on a column using the INSERT or UPDATE command overrides the table TTL.

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.

In INSERT statements, IF NOT EXISTS and USING TIMESTAMP are mutually exclusive. With IF NOT EXISTS, the timestamp is set by the lightweight transaction to ensure linearizable consistency.

IF NOT EXISTS

Inserts a new row of data only if no rows match the PRIMARY KEY values.

IF NOT EXISTS impacts performance because it is a lightweight transaction.

Data with time-to-live (TTL)

Columns and tables support an optional expiration period called the Time-to-Live (TTL). The TTL is the lifetime of the data in seconds. When the TTL expires, the data is automatically marked for deletion with a tombstone and eventually completely dropped through compaction or repair. Tombstoned data is scanned by database read operations, but it isn’t included in the returned data. Expired data is retained as tombstoned data in the database for the duration of gc_grace_seconds.

With INSERT and UPDATE statements, the USING TTL clause sets a specific lifetime for a value (column-level TTL). To change the column-level TTL, you must reinsert (UPDATE) the data with a new TTL. The column is upserted with the new TTL. To remove the TTL from a column, set the TTL to zero.

Use CREATE TABLE or ALTER TABLE to define the default TTL property for all columns in a table. A table-level TTL applies to all columns in the table unless overridden by a column-level TTL. If a value in any column exceeds the global TTL, the entire row is considered expired.

To determine the current time-to-live for a record, use the TTL function.

TTL requirements and limitations

TTL is not supported on counter columns.
TTL precision is one second, which is calculated by the coordinator node. When setting a TTL, ensure that all nodes in the cluster have synchronized clocks.
A very short TTL is not useful.
Expiring data uses additional 8 bytes of memory and disk space to record the TTL and grace period.

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 (-Dcassandra.expiration_date_overflow_policy) determines whether requests with expiration timestamps later than the maximum date are rejected or inserted.

Set a column-level TTL

Insert data into a table, and use the USING TTL clause to set the expiration period. This example sets the TTL to 200 seconds:

INSERT INTO cycling.calendar (
  race_id, race_name, race_start_date, race_end_date
) VALUES (
  200, 'placeholder', '2015-05-27', '2015-05-27'
)
USING TTL 200;

Change the column-level TTL

The time-to-live value can be updated with the USING TTL clause in an UPDATE statement. The example sets the TTL to 300 and updates the race name.

UPDATE cycling.calendar USING TTL 300
SET race_name = 'Tour de France - Stage 12'
WHERE race_id = 200
  AND race_start_date = '2015-05-27'
  AND race_end_date = '2015-05-27';

To remove a column-level TTL, set USING TTL to 0:

UPDATE cycling.calendar USING TTL 0
SET race_name = 'Tour de France - Stage 12'
WHERE race_id = 200
  AND race_start_date = '2015-05-27'
  AND race_end_date = '2015-05-27';

Get the TTL

Issue a SELECT statement with the TTL function to determine how much longer the data has to live:

SELECT TTL(race_name) FROM cycling.calendar WHERE race_id = 200;

The result prints the current TTL:

 ttl(race_name)
----------------
            300

(1 rows)

If you repeat the SELECT statement after some time, the time-to-live value will decrease:

 ttl(race_name)
----------------
            199

(1 rows)

Set TTL and timestamp

You can specify both a TTL and a timestamp for the inserted data.

TTL values represent the number of seconds until the data expires.

Timestamp values set the writetime for the inserted data as a number of microseconds since epoch.

Timestamp cannot be used with IF NOT EXISTS.

INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, 'KRUIKSWIJK', 'Steven'
)
USING TTL 86400
AND TIMESTAMP 123456789;

If you set TTL or timestamp on collection columns, the values apply to all elements of the collection.

INSERT INTO cycling.cyclist_sponsors_expire (
  cyclist_name, sponsorship
) VALUES (
  'PRIETO, Marcela',
  { 'Castrelli', 'Alfa Romeo' }
)
USING TIMESTAMP 100
AND TTL 10000;

Insert values into a collection

See Collections in CQL.

Write a user-defined type (UDT)

Set or replace individual values or an entire UDT using an INSERT or UPDATE statement.

Write an entire UDT record

Enclose the UDT with curly braces, separating each key-value pair with a colon:

INSERT INTO cycling.cyclist_stats
  (id, lastname, basics)
  VALUES (
    e7ae5cf3-d358-4d99-b900-85902fda9bb0,
    'FRAME',
    { birthday:'1993-06-18', nationality:'New Zealand', weight:null, height:null }
  );

Note the inclusion of null values for UDT elements that have no value. Your statement must include a value, null or otherwise, for each element of the UDT.

If the UDT is nested in another column type, such as collection, the UDT’s curly braces are enclosed within the collection’s syntax. For example, assume you have a list of races where the race name, date, and time are defined in a UDT. In this case, the UDT elements are enclosed in curly braces that are also enclosed in the list’s square brackets:

INSERT INTO cycling.cyclist_races (
  id,
  lastname,
  firstname,
  races
) VALUES (
  5b6962dd-3f90-4c93-8f61-eabfa4a803e2,
  'VOS',
  'Marianne',
  [ {
       race_title:'Rabobank 7-Dorpenomloop Aalburg',
       race_date:'2015-05-09',
       race_time:'02:58:33'},
     {
       race_title:'Ronde van Gelderland',
       race_date:'2015-04-19',
       race_time:'03:22:23'
     }
  ]
);

The UDT nested in the list is frozen, so the entire list is read when querying the table, and the entire list must be replaced when updating the list.

Write individual fields in a UDT

If a UDT isn’t frozen and doesn’t include collection fields, then you can update individual field values.

UDTs with collection fields must be frozen in the table definition. Therefore, you cannot update the individual fields in these UDTs. Instead, you must replace the entire UDT record.

For non-frozen, non-collection UDTs, use an UPDATE statement with the desired key-value pair definitions For example:

A UDT is defined in the table with basics basic_info:

CREATE TABLE IF NOT EXISTS cycling.cyclist_stats (
  id UUID PRIMARY KEY,
  lastname text,
  basics basic_info
);

A row is inserted with values for the UDT:

INSERT INTO cycling.cyclist_stats
  (id, lastname, basics)
  VALUES (
    220844bf-4860-49d6-9a4b-6b5d3a79cbfb,
    'TIRALONGO',
    { birthday:'1977-07-08', nationality:'Italy', weight:'63 kg', height:'1.78 m' }
  );

The UPDATE statement changes only the birthday field in the UDT:

UPDATE cycling.cyclist_stats SET basics.birthday = '2000-12-12'
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

Select the updated row:

SELECT * FROM cycling.cyclist_stats
  WHERE id = 220844bf-4860-49d6-9a4b-6b5d3a79cbfb;

The birthday value is changed, and the nationality, height, and weight values are unchanged:

 id                                   | basics                                                                                                                              | lastname
--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------+----------
 e7ae5cf3-d358-4d99-b900-85902fda9bb0 | {birthday: '1993-06-18 07:00:00.000000+0000', nationality: 'New Zealand', height: '72', weight: '175', next_race: 'Tour de France'} |    FRAME

(1 rows)

Use lightweight transactions (LWTs)

When an INSERT or UPDATE statement includes the IF clause, it triggers a lightweight transaction, also known as compare-and-set (CAS). A common use for lightweight transactions is an insertion operation that must be unique, such as user account creation.

LWTs use the Paxos protocol internally to ensure linearizable consistency. This causes a fourfold increase in latency to the round-trips necessary between the CAS coordinators. For more information, see Data consistency.

Consistency levels for LWTs

Specific consistency levels must be used for LWTs. For more information, see SERIAL CONSISTENCY.

Inequality operators for LWTs

Inequality conditions for lightweight transactions are supported. You can use <, ⇐, >, >=, != and IN operators in WHERE clauses to query tables with linearizable consistency. For example:

Insert a row that you will later change with a CAS operation.

In this example, the cyclist’s name is misspelled.

INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  4647f6d3-7bd2-4085-8d6c-1229351b5498, 'KNETEMANN', 'Roxxane'
)
IF NOT EXISTS;

Perform a CAS operation against the row by adding the predicate for the operation (IF) to the end of the query.

In this example, the firstname column is changed if the existing value is the misspelled value. If another operation already changed firstname (to the correct value or a different value altogether), the CAS operation will not apply.
```
UPDATE cycling.cyclist_name SET firstname = 'Roxane'
  WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498
  IF firstname = 'Roxxane';
```

IF NOT EXISTS and IF EXISTS are LWTs

The IF NOT EXISTS and IF EXISTS clauses check for the presence or absence of a row or database object before performing an operation. These are useful clauses, but they are LWTs because they must read the current state before proceeding (read-before-write).

For example, insert a row only if it doesn’t already exist:

INSERT INTO cycling.cyclist_name (
  id, lastname, firstname
) VALUES (
  c4b65263-fe58-4846-83e8-f0e1c13d518f, 'RATTO', 'Rissella'
)
IF NOT EXISTS;

Uniquely, IF NOT EXISTS returns output about whether the operation was applied. Whereas, without IF NOT EXISTS, the command returns no standard output.

In this example, the specified row already exists, so the command returns false in the [applied] column along with the existing values for the row:

 [applied] | id                                   | firstname | lastname
-----------+--------------------------------------+-----------+----------
     False | c4b65263-fe58-4846-83e8-f0e1c13d518f |  Rissella |    RATTO

If the specified row doesn’t exist, the INSERT is applied, and the command returns true in the [applied] column of the results:

 [applied]
-----------
      True

Insert JSON-formatted values

The INSERT CQL commands supports JSON. This is useful for testing and debugging inserts of JSON data to your tables.

Use the following syntax to insert JSON data, enclosing all non-number values in double quotes. Booleans, UUID, and other CQL data types must be in double quotes.

INSERT INTO [<keyspace_name>.]<table_name> JSON
'{
  "column_name": value [,…]
}'
( DEFAULT ( NULL | UNSET) );

For example:

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "GC",
  "points" : 780,
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda",
  "lastname" : "SUTHERLAND"
}';

For a new row, only the primary key fields are required. Any other column not defined in the JSON is set to null.

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint",
  "points" : 700,
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}';

Null versus unset

By default (or if DEFAULT NULL is explicitly used), a column omitted from the JSON map is set to NULL. This means that any pre-existing value for that column is removed, resulting in a tombstone.

Alternatively, if you use DEFAULT UNSET after the value, omitted column values are left unset, which preserves any pre-existing values in that column.

The following example removes the lastname value "SUTHERLAND" from the previous example because the default behavior is to mark unspecified values as null:

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint",
  "points" : 780,
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda"
}';

Use the DEFAULT UNSET option to only overwrite the values given in the JSON string:

INSERT INTO cycling.cyclist_category JSON '{
  "category" : "Sprint",
  "points" : 780,
  "id" : "829aa84a-4bba-411f-a4fb-38167a987cda" }'
DEFAULT UNSET;

Use a SELECT statement to check the contents of the row and see if any values are null or unset:

SELECT * FROM cycling.cyclist_category;

 category      | points | id                                   | lastname
---------------+--------+--------------------------------------+------------
 One-day-races |    367 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
 One-day-races |    198 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
    Time-trial |    182 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
    Time-trial |      3 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |    780 | 829aa84a-4bba-411f-a4fb-38167a987cda |       null
        Sprint |    700 | 829aa84a-4bba-411f-a4fb-38167a987cda |       null
        Sprint |     39 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
        Sprint |      0 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
            GC |   1324 | 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47 | KRUIJSWIJK
            GC |   1269 | 220844bf-4860-49d6-9a4b-6b5d3a79cbfb |  TIRALONGO
            GC |    780 | 829aa84a-4bba-411f-a4fb-38167a987cda | SUTHERLAND

(11 rows)

Insert complex strings with dollar quotes

Insert complex string constants using double dollar signs ($$) to enclose a string with quotes, backslashes, or other characters that would normally need to be escaped:

INSERT INTO cycling.calendar
  (race_id, race_start_date, race_end_date, race_name)
   VALUES
  (201, '2015-02-18', '2015-02-22', $$Women's Tour of New Zealand$$);