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:

  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.

booktown=# 
CREATE TABLE favorite_books

booktown-# 
             (employee_id integer, books text[]);

CREATE

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.

booktown=# 
CREATE TABLE favorite_authors (employee_id integer,

booktown(# 
                               authors_and_titles text[][]);

CREATE