Inserting Data in Cassandra

INSERT Basics in CQL

Inserting data in Cassandra works differently than in relational databases. CQL INSERT is actually an upsert operation by default: if a row with the same primary key already exists, it will be overwritten without any error. This behavior is fundamental to how Cassandra handles writes in a distributed environment.

Basic INSERT Syntax

The INSERT statement requires you to specify all columns in the primary key. Non-key columns are optional and can be omitted.

-- Basic insert
INSERT INTO users (user_id, name, email, created_at)
VALUES (uuid(), 'Alice Johnson', 'alice@example.com', toTimestamp(now()));

-- Insert with a specific UUID
INSERT INTO users (user_id, name, email, created_at)
VALUES (550e8400-e29b-41d4-a716-446655440000, 'Bob Smith', 'bob@example.com', '2025-06-15T10:30:00Z');

Upsert Behavior

Since INSERT is an upsert, inserting a row with the same primary key silently replaces the existing data. This means you do not get duplicate key errors like in SQL databases.

-- First insert creates the row
INSERT INTO products (product_id, name, price)
VALUES ('prod-001', 'Widget', 9.99);

-- Second insert with same key OVERWRITES the row
INSERT INTO products (product_id, name, price)
VALUES ('prod-001', 'Super Widget', 14.99);
-- The row now has name='Super Widget' and price=14.99

Conditional Inserts with IF NOT EXISTS

To prevent overwriting existing data, use IF NOT EXISTS. This triggers a lightweight transaction (Paxos consensus), which is slower but guarantees the insert only happens if the row does not already exist.

-- Only insert if the user doesn't already exist
INSERT INTO users (user_id, name, email)
VALUES (uuid(), 'Charlie Brown', 'charlie@example.com')
IF NOT EXISTS;

-- Returns [applied] = true if inserted, false if row existed

INSERT with TTL

You can set a Time-To-Live (TTL) on inserted data so it automatically expires after a given number of seconds. This is useful for session tokens, temporary caches, or event data.

-- Insert with 1-hour TTL (3600 seconds)
INSERT INTO sessions (session_id, user_id, token)
VALUES (uuid(), 550e8400-e29b-41d4-a716-446655440000, 'abc123token')
USING TTL 3600;

-- Insert with TTL and a specific timestamp
INSERT INTO events (event_id, event_type, payload)
VALUES (uuid(), 'page_view', '{"url": "/home"}')
USING TTL 86400 AND TIMESTAMP 1718467200000000;

Working with Data Types

CQL supports various data types. Here are examples of inserting different types:

-- UUID, text, int, float, boolean, timestamp
INSERT INTO sensor_data (sensor_id, reading_time, temperature, is_active, label)
VALUES (uuid(), toTimestamp(now()), 23.5, true, 'Lab Sensor A');

-- Blob data (hex-encoded)
INSERT INTO files (file_id, content)
VALUES (uuid(), textAsBlob('Hello World'));

Key Takeaways

• INSERT in Cassandra is an upsert by default -- it overwrites existing rows with the same primary key.
• Use IF NOT EXISTS for conditional inserts when you need to prevent overwrites.
• TTL allows automatic data expiration, ideal for temporary or session data.
• All primary key columns must be provided in every INSERT statement.