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.