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 ...

Get Practical PostgreSQL now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.