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
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

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.

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,

                               authors_and_titles text[][]);

Databases - Practical PostgreSQL
Previous Page Home Next Page

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