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:
single_array type -- A single array of values.
Additional square brackets may be added to create
may store an array of array values. For example:
multi_array type -- A multidimensional array of values.
In theory, an integer value
could be supplied within the square brackets to produce a
array (one which always has
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
Example 7-16. Creating a table with an array column
CREATE TABLE favorite_books
(employee_id integer, books text);
The table created by Example 7-16 allows any number of book titles to be stored
in a single array column, for each employee. The advantage of such an array of discrete text values over a single text
string (which also, of course, could contain multiple titles) is that each title is kept physically separate from each
other title in the array column. Since the system knows where each array value begins and ends, you can choose titles by
their subscript, rather than having to manually parse them out of a long text string.
Creating a multidimensional array column is very similar. The only distinction is that another pair of square
brackets follows the first pair, as shown earlier in this section. Example 7-17 creates a table called
favorite_authors, with an employee_id column of type
integer, and multidimensional text array of
author_and_titles. This essentially creates an array of text arrays.
Example 7-17. Creating a table with a multidimensional array column
CREATE TABLE favorite_authors (employee_id integer,