INSERT

Add or update columns.

Add or update columns.

Synopsis

INSERT INTO keyspace_name.table_name
  ( identifier, column_name...)
  VALUES ( value, value ... ) IF NOT EXISTS
  USING option AND option

value is one of:

  • a literal
  • a set
    { literal, literal, . . . }
  • a list
    [ literal, literal, . . . ]
  • a map collection, a JSON-style array of literals
    { literal : literal, literal : literal, . . . }

option is one of:

  • TIMESTAMP microseconds
  • TTL seconds
Table 1. Legend
  • Uppercase means literal
  • Lowercase means not literal
  • Italics mean optional
  • The pipe (|) symbol means OR or AND/OR
  • Ellipsis (...) means repeatable

A semicolon that terminates CQL statements is not included in the synopsis.

Description

An INSERT writes one or more columns to a record in a Cassandra table atomically and in isolation. No results are returned. You do not have to define all columns, except those that make up the key. Missing columns occupy no space on disk.

Note:

After the partition key, any column that is part of the primary key is used as a clustering key. In Cassandra2.1 and earlier, you cannot insert any value larger than 64K bytes into a clustering column.

If the column exists, it is updated. The row is created if none exists. Use IF NOT EXISTS to perform the insertion only if the row does not already exist. Using IF NOT EXISTS incurs a performance hit associated with using Paxos internally. For information about Paxos, see Cassandra 2.1 documentation.

You can qualify table names by keyspace. INSERT does not support counters, but UPDATE does. Internally, insert and update operations are identical.

Specifying TIMESTAMP and TTL

  • Time-to-live (TTL) in seconds
  • Timestamp in microseconds
INSERT INTO Hollywood.NerdMovies (user_uuid, fan)
  VALUES (cfd66ccc-d857-4e90-b1e5-df98a3d40cd6, 'johndoe')
  USING TTL 86400;

TTL input is in seconds. TTL column values are automatically marked as deleted (with a tombstone) after the requested amount of time has expired. TTL marks the inserted values, not the column itself, for expiration. Any subsequent update of the column resets the TTL to the TTL specified in the update. By default, values never expire. You cannot set data in a counter column to expire.

The TIMESTAMP input is in microseconds. If not specified, the time (in microseconds) that the write occurred to the column is used.
INSERT INTO cycling.calendar (race_id, race_name, race_start_date, race_end_date) 
VALUES (200, 'placeholder', '2015-05-27', '2015-05-27') 
USING TIMESTAMP 123456789;

Using a collection set or map

To insert data into a collection, enclose values in curly brackets. Set values must be unique. For example:
INSERT INTO users (userid, first_name, last_name, emails)
  VALUES('frodo', 'Frodo', 'Baggins', {'f@baggins.com', 'baggins@gmail.com'});
Insert a map named todo to insert a reminder, 'die' on October 2 for user frodo.
INSERT INTO users (userid, todo )
  VALUES('frodo', {'2014-10-2 12:10' : 'die' } );

Values of items in collections are 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-83e8-f0e1c13d518f', 'RATTO', 'Risselda') 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:

Standard output if IF NOT EXISTS returns 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:

Standard output if IF NOT EXISTS returns FALSE
Note: Using IF NOT EXISTS incurs a performance hit associated with using Paxos internally. For information about Paxos, see Cassandra 3.0 documentation.