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

Selecting Values From Array Columns

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.

Array subscripts

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)

Array slices

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.

Array dimensions

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

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