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', not Alice or "Alice".
  • One row per statement. Some databases let you add many rows in one INSERT. CQL does not, so you write one INSERT for 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.