Lists: Ordered Values

A list is a collection that keeps its values in order. Unlike a set, a list can hold the same value more than once.

CREATE TABLE todos (
  username text PRIMARY KEY,
  tasks list<text>
);

Write a list

Put the values inside square brackets [ ]:

INSERT INTO todos (username, tasks)
VALUES ('kim', [
  'write tests',
  'fix bug',
  'write tests'
]);

SELECT * FROM todos;

Output:

 username | tasks
----------+-------------------------------------------
      kim | ['write tests', 'fix bug', 'write tests']

(1 rows)

The values come back in the order you wrote them. 'write tests' is in the list two times. A set would keep it only once.

Add to the end or the front

Use + to join a new list to the old one. Put the new list after tasks to add at the end, or before tasks to add at the front:

-- add to the end
UPDATE todos
SET tasks = tasks + ['update docs']
WHERE username = 'kim';

-- add to the front
UPDATE todos
SET tasks = ['urgent fix'] + tasks
WHERE username = 'kim';

SELECT * FROM todos;

Output:

 username | tasks
----------+------------------------------------------------------------------------
      kim | ['urgent fix', 'write tests', 'fix bug', 'write tests', 'update docs']

(1 rows)

Remove a value

- removes every copy of a value:

UPDATE todos
SET tasks = tasks - ['write tests']
WHERE username = 'kim';

SELECT * FROM todos;

Output:

 username | tasks
----------+------------------------------------------
      kim | ['urgent fix', 'fix bug', 'update docs']

(1 rows)

Both copies of 'write tests' are gone.

Change a value by its position

Positions start at 0. So tasks[0] is the first value:

UPDATE todos
SET tasks[0] = 'call client'
WHERE username = 'kim';

SELECT * FROM todos;

Output:

 username | tasks
----------+-------------------------------------------
      kim | ['call client', 'fix bug', 'update docs']

(1 rows)

This works, but it is slower than a normal write. To change a value by its position, Cassandra must first read the list. Adding with + needs no read.

List or set?

Lists need more work from Cassandra than sets. So use a list only when the order matters or when you need the same value more than once. Otherwise, use a set.

In the exercise, sam has one more task. Add it to the end of his list.