Learning PostgreSQL 11 - Third Edition

Modifying and accessing arrays

An array element can be accessed via an index; if the array doesn't contain an element for this index, the NULL value is returned, as shown in the following example:

CREATE TABLE color(  color text []);INSERT INTO color(color) VALUES ('{red, green}'::text[]);INSERT INTO color(color) VALUES ('{red}'::text[]);

To confirm that NULL is returned, let's run the following code:

car_portal=> SELECT color [3] IS NOT DISTINCT FROM null FROM color; ?column? ---------- t t(2 rows)

Also, an array can be sliced by providing a lower and upper bound, as follows:

car_portal=> SELECT color [1:2] FROM color;    color ------------- {red,green} {red}(2 rows)

When updating an array, you could completely replace the array, get a slice, ...