Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Updating Values in Array Columns

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)
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire