Was this page helpful?
You're viewing documentation for an unstable version of ScyllaDB Enterprise. Switch to the latest stable version.
Inserting data for a row is done using an
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` )
INSERT INTO NerdMovies (movie, director, main_actor, year) VALUES ('Serenity', 'Joss Whedon', 'Nathan Fillion', 2005) USING TTL 86400 IF NOT EXISTS;
INSERT statement writes one or more columns for a given row in a table. Note that since a row is identified by
PRIMARY KEY, at least the columns composing it must be specified. The list of columns to insert to must be
supplied when using the
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
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
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.
© 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.