Cassandra INSERT Data

The INSERT statement adds a new row to a Cassandra table. Unlike SQL inserts in a relational database, a Cassandra INSERT is also an upsert — if a row with the same primary key already exists, the INSERT silently updates it instead of throwing a duplicate key error. Understanding this behavior helps you avoid accidental data loss.

Basic INSERT Syntax

INSERT INTO keyspace_name.table_name
  (column1, column2, column3, ...)
VALUES
  (value1, value2, value3, ...)
[USING TTL seconds]
[USING TIMESTAMP microseconds];

Simple INSERT Example

USE ecommerce;

INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (uuid(), 'Alice', 'Smith', 'alice@example.com');

The uuid() function generates a fresh UUID for the customer_id column. You can also supply your own UUID value.

INSERT with a Specific UUID

INSERT INTO customers (customer_id, first_name, email)
VALUES (
  11111111-1111-1111-1111-111111111111,
  'Bob',
  'bob@example.com'
);

Upsert Behavior

When you insert a row whose primary key already exists in the table, Cassandra merges the new column values with the existing row rather than rejecting the insert. This is called an upsert (update + insert).

-- First insert:
INSERT INTO customers (customer_id, first_name, email)
VALUES (uuid-A, 'Alice', 'alice@example.com');

-- Second insert with same primary key but different columns:
INSERT INTO customers (customer_id, last_name)
VALUES (uuid-A, 'Smith');

-- Result: ONE row with both first_name and last_name filled
customer_id | first_name | last_name | email
────────────────────────────────────────────
uuid-A      | Alice      | Smith     | alice@example.com

INSERT IF NOT EXISTS

To prevent overwriting an existing row, add the IF NOT EXISTS clause. Cassandra checks whether the primary key already exists and skips the insert if it does. This uses a lightweight transaction internally, so it is slower than a regular insert.

INSERT INTO customers (customer_id, first_name, email)
VALUES (uuid-A, 'Alice', 'alice@example.com')
IF NOT EXISTS;

The response includes an [applied] column:

 [applied] | customer_id | ...
-----------+-------------+----
 True      |             |      -- row was inserted
 False     | uuid-A      | ...  -- row already existed; skipped

INSERT with TTL (Time To Live)

TTL tells Cassandra to automatically delete the row after a set number of seconds. This is useful for sessions, caches, and temporary data.

-- Insert a session token that expires in 30 minutes (1800 seconds):
INSERT INTO session_tokens (token, user_id, created_at)
VALUES (uuid(), uuid-A, toTimestamp(now()))
USING TTL 1800;

Timeline:

INSERT at 10:00 AM ──▶ row visible
10:30 AM          ──▶ TTL expires, Cassandra marks row deleted
Next compaction   ──▶ tombstone removed permanently

Check Remaining TTL on a Row

SELECT TTL(user_id) FROM session_tokens
WHERE token = [your-token-uuid];

 ttl(user_id)
──────────────
 1543           -- seconds remaining

INSERT with a Custom Timestamp

Cassandra uses timestamps to resolve conflicts between replicas. You can override the automatic timestamp with your own value (in microseconds since the Unix epoch). This is useful when importing historical data.

INSERT INTO events (event_id, event_name)
VALUES (uuid(), 'System Start')
USING TIMESTAMP 1710000000000000;

Inserting Collection Values

-- LIST:
INSERT INTO playlists (playlist_id, name, songs)
VALUES (uuid(), 'Workout Mix', ['Track A', 'Track B', 'Track C']);

-- SET:
INSERT INTO articles (article_id, title, tags)
VALUES (uuid(), 'Intro to CQL', {'cql', 'cassandra', 'beginner'});

-- MAP:
INSERT INTO user_prefs (user_id, settings)
VALUES (uuid(), {'theme': 'dark', 'lang': 'en'});

Batch Inserts

You can group multiple INSERT statements in a BATCH to send them in a single network round trip. Use UNLOGGED BATCH for inserts within the same partition — it skips the batch log and is much faster.

BEGIN UNLOGGED BATCH
  INSERT INTO orders (order_id, customer_id, total)
  VALUES (uuid(), uuid-A, 99.99);

  INSERT INTO orders (order_id, customer_id, total)
  VALUES (uuid(), uuid-A, 49.50);
APPLY BATCH;

Avoid using BATCH to write to multiple different partitions in a performance-sensitive context — that pattern adds overhead rather than reducing it.

NULL Values in INSERT

You cannot explicitly insert NULL into a Cassandra column. Omit the column from the INSERT statement instead. If you do include NULL, Cassandra writes a tombstone (a deletion marker), which can cause performance issues if done repeatedly.

-- Correct: omit columns with no data
INSERT INTO customers (customer_id, first_name)
VALUES (uuid(), 'Carol');

-- Avoid: inserting NULL creates a tombstone
INSERT INTO customers (customer_id, first_name, email)
VALUES (uuid(), 'Dave', null);

Summary

Cassandra INSERT adds a row or merges values with an existing row sharing the same primary key. Use IF NOT EXISTS when you need true insert-only semantics. Use USING TTL to auto-expire temporary data and USING TIMESTAMP to control conflict resolution when importing historical records. Avoid inserting NULL — omit columns instead. For multiple inserts to the same partition, UNLOGGED BATCH reduces network round trips efficiently.

Previous lesson

Back to course

Next lesson