Selecting an array column from a table will result in the entire array being returned in the same constant format
described in the previous section. Example 7-20 retrieves the entire arrays for inserted
rows in the books column of the favorite_books
table.
Example 7-20. Selecting entire array values
booktown=#
SELECT books FROM favorite_books;
books
------------------------------------------
{"The Hitchhiker's Guide to the Galaxy"}
{"The Hobbit","Kitten, Squared"}
(2 rows)
While it is helpful to be able to return the entire array, the ability to retrieve only a specific portion of an
array is often more useful. To this end, you need to learn how to work with array subscripts and slices.
The usefulness of arrays lies largely in the fact that you can use
subscripts
to specify the
value that you wish to view. A subscript is an integer value surrounded by square brackets, that describes the value you
want to select. This number describes the precedence of the value you wish to select, from left to right in the
array.
Unlike arrays in programming languages such as C, PostgreSQL begins counting array elements at 1, not
0. Example 7-21 uses the [1]
subscript on the books column of the favorite_books table
to select only the first of an employee's favorite titles. Notice that the query returns values without braces or double-quotes.
This is because a single text value need only be returned as a single text constant, not as an array.
Example 7-21. Selecting array values with subscripts
booktown=#
SELECT books[1] FROM favorite_books;
books
--------------------------------------
The Hitchhiker's Guide to the Galaxy
The Hobbit
(2 rows)
Specifying a subscript for an array element which holds no value results in a NULL
value being selected. The IS NOT NULL keywords may be useful in conjunction with such
selections. Example 7-22 demonstrates two queries; the first returns two rows,
NULL value as well as a title. The second query only returns the title (and not the row
with the NULL value as a result of its use of the WHERE
clause, with the IS NOT NULL condition.
Example 7-22. Avoiding NULL values in arrays
booktown=#
SELECT books[2] FROM favorite_books;
books
-----------------
Kitten, Squared
(2 rows)
booktown=#
SELECT books[2] FROM favorite_books
booktown-#
WHERE books[2] IS NOT NULL;
books
-----------------
Kitten, Squared
(1 row)
Selecting from a multidimensional array requires an additional subscript following the initial subscript. The first
subscript refers to which array that you are retrieving data from, while the second subscript refers to which member of
the specified array is to be retrieved. Example 7-23 demonstrates selecting the
first author, and associated title for that author, from the favorite_authors table created
in Example 7-19.
Example 7-23. Selecting From a Multi-Dimensional Array
booktown=#
SELECT authors_and_titles[1][1] AS author,
booktown-#
authors_and_titles[1][2] AS title
booktown-#
FROM favorite_authors;
author | title
----------------+------------------
J.R.R. Tolkien | The Silmarillion
(1 row)
PostgreSQL also supports
slices
in array selection. These are similar to array subscripts, but
describe a
range
of values to be returned. The syntax of a slice is a pair of integers, separated by a
colon (:), surrounded by square brackets. For example,
[2:5] specifies the second, third, fourth, and fifth array values of a given
array. The result of a slice selection is returned as an array constant that is essentially a sub-array of the entire
array (though a slice may extend from the beginning to the end of an array).
Example 7-24 selects the range of the first two book titles in the
books test array column from the favorite_books table.
Even though the first returned row has only one title, it is still returned as an array with one member value.
Example 7-24. Selecting array values with slices
booktown=#
SELECT books[1:2] FROM favorite_books;
books
------------------------------------------
{"The Hitchhiker's Guide to the Galaxy"}
{"The Hobbit","Kitten, Squared"}
(2 rows)
Array slices can be somewhat unpredictable with multidimensional arrays as of PostgreSQL 7.1.x. It is therefore
recommended to stick to exact subscript values when working with multidimensional arrays until this support is
improved.
It can be useful to know the number of values stored in an array. You may use the
array_dims() function to accomplish this. It accepts as a parameter a single identifier,
which is the name of the array column that you wish to perform the function on. The result is returned as a character
string describing the array with the same syntax used in array slices. Example 7-25 calls the
array_dims() function on the books column of the
favorite_books table.
Example 7-25. Using array_dims( )
booktown=#
SELECT array_dims(books) FROM favorite_books;
array_dims
------------
[1:1]
[1:2]
(2 rows)
