Values in array columns may be modified in one of three ways:
- Complete modification
-
The entire array may be replaced with a new array constant.
- Slice modification
-
A slice of an array (range between two values) may be replaced with a new array constant. The new array
constant should have the same number of values within it as the splice to be updated.
- Element modification
-
An individual value in the array may be replaced with a new constant of the base type of the array. You
use a subscript to specify which array value to replace.
Replacing an array value with a new array puts no restriction on the number of values within the new array. There
need not be the same number of values in the new array as in the existing one.
For instance, suppose that the employee with id 102 wishes to add another favorite book to his
list in the favorite_books table. This is achieved with an
UPDATE statement in Example 7-26 which completely
overwrites the previous value.
Example 7-26. Completely modifying an array
booktown=#
UPDATE favorite_books
booktown-#
SET books='{"The Hitchhiker\'s Guide to the Galaxy",
booktown'#
"The Restaurant at the End of the Universe"}'
booktown-#
WHERE employee_id = 102;
UPDATE 1
The same approach used in Example 7-26 can be used to set a slice of an array by
attaching a slice descriptor to the end of the target identifier (e.g., books[1:3] would
refer to the first, second and third values in the books array column). More commonly,
though, situations arise where a single value within an array needs to be modified, instead of the entire array, or a slice
of an array.
Updating a single value in an array is done by attaching a subscript to the target identifier to indicate the
specific value to be modified. Example 7-27 updates the first array value of the
books column, in the favorite_books table.
Example 7-27. Modifying an array subscript
booktown=#
SELECT books[1] FROM favorite_books;
books
------------------------------------------
The Hitchhiker's Guide to the Galaxy
The Hobbit
(2 rows)
booktown=#
UPDATE favorite_books
booktown-#
SET books[1] = 'There and Back Again: A Hobbit\'s Holiday'
booktown-#
WHERE books[1] = 'The Hobbit';
UPDATE 1
booktown=#
SELECT books[1] FROM favorite_books;
books
------------------------------------------
The Hitchhiker's Guide to the Galaxy
There and Back Again: A Hobbit's Holiday
(2 rows)
