List column

Write one or more values into a non-frozen list column. If a table specifies a list to hold data, then use INSERT or UPDATE to enter data.

A frozen list column can only insert or update the entire list, and elements cannot be prepended or appended to the list.

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' ] );

INSERT INTO cycling.upcoming_calendar ( year, month, events ) 
VALUES ( 2015, 07, [ 'Tour de France' ] );

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;

The method of removing elements using an indexed position from a list 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 the UPDATE statement as shown in this example is recommended over the last example because it is safer and faster.

List column

Prerequisites

Procedure

Was this helpful?

Give Feedback