Caution

You're viewing documentation for an unstable version of ScyllaDB Enterprise. Switch to the latest stable version.

ScyllaDB Docs ScyllaDB Enterprise CQL Reference Data Manipulation INSERT

INSERT¶

Inserting data for a row is done using an INSERT statement:

insert_statement:  INSERT INTO table_name ( `names_values` | `json_clause` )
                :  [ IF NOT EXISTS ]
                :  [ USING `update_parameter` ( AND `update_parameter` )* ];
names_values: `names` VALUES `tuple_literal`
json_clause:  JSON `string` [ DEFAULT ( NULL | UNSET ) ]
names: '(' `column_name` ( ',' `column_name` )* ')'
update_parameter: ( TIMESTAMP `int_value` | TTL  `int_value` | TIMEOUT `duration` )
int_value: ( `integer` | `bind_marker` )

For example:

INSERT INTO NerdMovies (movie, director, main_actor, year)
      VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005)
      USING TTL 86400 IF NOT EXISTS;

The INSERT statement writes one or more columns for a given row in a table. Note that since a row is identified by its PRIMARY KEY, at least the columns composing it must be specified. The list of columns to insert to must be supplied when using the VALUES syntax.

Note that unlike in SQL, INSERT does not check the prior existence of the row by default: the row is created if none existed before, and updated otherwise. Furthermore, there is no means to know which of creation or update happened.

All updates of an INSERT are applied atomically, meaning the statement can not have a partial effect on database state.

It can, however, leave some of the columns unchanged due to the semantics of eventual consistency on an event of a timestamp collision:

INSERT statements happening concurrently at different cluster nodes proceed without coordination. Eventually cell values supplied by a statement with the highest timestamp will prevail (see update ordering).

Unless a timestamp is provided by the client, ScyllaDB will automatically generate a timestamp with microsecond precision for each column assigned by INSERT. ScyllaDB ensures timestamps created by the same node are unique. Timestamps assigned at different nodes are not guaranteed to be globally unique. With a steadily high write rate timestamp collision is not unlikely. If it happens, i.e. two INSERTS have the same timestamp, a conflict resolution algorithm determines which of the inserted cells prevails (see update ordering).

Please refer to the update parameters section for more information on the update_parameter.

INSERT INTO NerdMovies  (movie, director, main_actor)
VALUES ('Serenity', 'Anonymous', 'Unknown')
USING TIMESTAMP  1442880000000000;
INSERT INTO NerdMovies  (movie, director, main_actor)
VALUES ('Serenity', 'Joseph Whedon', 'Nathan Fillion')
USING TIMESTAMP 1442880000000000;

SELECT movie, director, main_actor FROM NerdMovies WHERE movie = 'Serenity'

movie    | director      | main_actor | year
----------+---------------+------------+------
Serenity | Joseph Whedon |    Unknown | null

INSERT is not required to assign all columns, so if two statements modify the same primary key but assign different columns effects of both statements are preserved:

INSERT INTO NerdMovies (movie, director, main_actor)
      VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion');
INSERT INTO NerdMovies (movie, director, main_actor, year)
      VALUES ('Serenity', 'Josseph Hill Whedon', 2005);
SELECT * FROM NerdMovies WHERE movie = 'Serenity'

╭─────────┬───────────────────┬──────────────┬─────╮
│movie    │director           │main_actor    │year │
├─────────┼───────────────────┼──────────────┼─────┤
│Serenity │Joseph Hill Whedon │Nathan Fillion│2005 │
╰─────────┴───────────────────┴──────────────┴─────╯

Also note that INSERT does not support counters, while UPDATE does.

Note

You can use the IF NOT EXISTS condition with the INSERT statement. When this is used, the insert is only made if the row does not exist prior to the insertion. Each such INSERT gets a globally unique timestamp. Using IF NOT EXISTS incurs a non-negligible performance cost (internally, as Paxos will be used), so use IF NOT EXISTS wisely.

If enabled on a table, you can use UPDATE, INSERT, and DELETE statements with Change Data Capture (CDC) tables.

Apache Cassandra Query Language (CQL) Reference

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.

Was this page helpful?