List fields
Write one or more values into a list column.
If a table specifies a list to hold data, then use
INSERT
or UPDATE
to enter data.
Procedure
-
Insert data into the list, enclosing values in square
brackets.
INSERT INTO cycling.upcoming_calendar ( year, month, events ) VALUES ( 2015, 06, [ 'Criterium du Dauphine', 'Tour de Suisse' ] );
-
Use the
UPDATE
statement to insert values into the list. Prepend an element to the list by enclosing it in square brackets and using the addition (+) operator.UPDATE cycling.upcoming_calendar SET events = [ 'Tour de France' ] + events WHERE year = 2015 AND month = 06;
-
Append an element to the list by switching the order of
the new element data and the list name in the
UPDATE
statement.UPDATE cycling.upcoming_calendar SET events = events + [ 'Tour de France' ] WHERE year = 2017 AND month = 05;
These update operations are implemented internally without any read-before-write. Appending and prepending a new element to the list writes only the new element. -
Add an element at a particular position using the list
index position in square brackets.
UPDATE cycling.upcoming_calendar SET events[2] = 'Vuelta Ciclista a Venezuela' WHERE year = 2015 AND month = 06;
To add an element at a particular position, the database reads the entire list, and then rewrites the part of the list that needs to be shifted to the new index positions. Consequently, adding an element at a particular position results in greater latency than appending or prefixing an element to a list. -
Remove an element from a list, use the
DELETE
statement and the list index position in square brackets. For example, remove the first event in the list.DELETE events[0] FROM cycling.upcoming_calendar WHERE year = 2015 AND month = 07;
Warning: The method of removing elements using an indexed position from alist
requires an internal read. In addition, the client-side application could only discover the indexed position by reading the whole list and finding the values to remove, adding additional latency to the operation. If another thread or client prepends elements to the list before the operation is done, incorrect data will be removed. -
Remove all elements having a particular value using the
UPDATE
statement, the subtraction operator (-), and the list value in square brackets.UPDATE cycling.upcoming_calendar SET events = events - ['Tour de France Stage 10'] WHERE year = 2015 AND month = 07;
Using theUPDATE
statement as shown in this example is recommended over the last example because it is safer and faster.