Practical PostgreSQL

Arrays

As documented in Chapter 3, PostgreSQL supports non-atomic values in individual table columns through data constructs called arrays. An array itself is not a data type, but an extension of any PostgreSQL data type.

Creating an Array Column

A simple array column is created by appending a pair of square brackets to the data type of the intended array column within a CREATE TABLE or ALTER TABLE statement. These brackets indicate that more than a single value of the described data type may be inserted without limit into that column.

For example, the following shows the syntax for a column named single_array, which is an array column of type type:

single_array type[]   -- A single array of values.

Additional square brackets may be added to create multidimensional arrays, which may store an array of array values. For example:

multi_array  type[][] -- A multidimensional array of values.

In theory, an integer value n could be supplied within the square brackets to produce a fixed-length array (one which always has n members, and no more). As of PostgreSQL 7.1.x, however, this restriction is not enforced, and there is no practical difference between an array created with a fixed length and one created without.

Example 7-16 creates a table named favorite_books. This table associates an integer value of an employee with a one-dimensional character string array of type text called books.

Example 7-16. Creating a table with an array column

booktown=# CREATE TABLE favorite_books
booktown-#              (employee_id ...