INSERT INTO: Adding Rows
You add rows to a table with INSERT INTO. For the examples below, the employees table starts empty.
Syntax
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
You list the columns first, then the values in the same order.
Example
INSERT INTO employees
(id, name, age, department, gender)
VALUES (1, 'Alice', 28, 'HR', 'female');
SELECT * FROM employees;
Output:
id | age | department | gender | name
----+-----+------------+--------+-------
1 | 28 | HR | female | Alice
(1 rows)
A few rules:
- Text values need single quotes:
'Alice', notAliceor"Alice". - One row per statement. Some databases let you add many rows in one
INSERT. CQL does not, so you write oneINSERTfor each row.
Inserting only some columns
You don't have to give a value for every column:
INSERT INTO employees (id, name)
VALUES (2, 'Bob');
SELECT * FROM employees;
Output:
id | age | department | gender | name
----+------+------------+--------+-------
1 | 28 | HR | female | Alice
2 | null | null | null | Bob
(2 rows)
Bob has no age, department or gender, so those columns show null. null means "no value".
The primary key is the one column you can't skip. Without it, Cassandra doesn't know where to store the row:
INSERT INTO employees (name, age)
VALUES ('Nina', 27);
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Some partition key parts are missing: id"
Here the primary key id is also the partition key, the value that decides which node stores the row.
Same key, no error
What if you insert a row with an id that already exists? Many databases stop with a "duplicate key" error. Cassandra does not:
INSERT INTO employees
(id, name, age, department, gender)
VALUES (1, 'Alice', 29, 'HR', 'female');
SELECT * FROM employees;
Output:
id | age | department | gender | name
----+------+------------+--------+-------
1 | 29 | HR | female | Alice
2 | null | null | null | Bob
(2 rows)
There are still 2 rows. The new values replaced the old row, so Alice's age is now 29. This is called an upsert (update or insert). You will learn more about it in the Modify Data section.
Now add a new employee to the table in the exercise.