Was this page helpful?
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, Scylla will automatically
generate a timestamp with microsecond precision for each
column assigned by INSERT
. Scylla 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
New in Scylla Open Source 3.2, 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
Copyright
© 2016, The Apache Software Foundation.
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?