Inserting Values into Array Columns

A special kind of syntax is used in order to insert multiple values into a single column. This syntax allows you to describe an array constant . As documented in Chapter 3, the syntax of an array constant (for referring to PostgreSQL array values in SQL statements) is a special arrangement of curly braces, double-quotes and commas, all bound by single-quotes. Double quotes are required only when working with an array of character strings. Therefore, the general forms of array constants are:

  '{ "text1" [, ...] }' -- A character string array.
  '{ numeric [, ...] }' -- A numeric array.

These syntax forms illustrate how to handle string and numeric arrays, but a column may be defined as an array of any arbitrary type (including boolean, date, and time types). Generally, if you would ordinarily use single-quotes to describe a value in a non-array context (such as with a string constant, or timestamp value), double-quotes should be used for that value in an array constant.

Example 7-18 inserts a pair of records into the favorite_books table. The first statement inserts a single favorite book for the employee with id 102, and the second statement inserts two titles for the employee with id 103.

Example 7-18 executes two SQL INSERT statements, which insert a pair of array constant values.

booktown=# 
INSERT INTO favorite_books VALUES 

booktown-# 
            (102, '{"The Hitchhiker\'s Guide to the Galaxy"}');

INSERT 3628399 1
booktown=# 
INSERT INTO favorite_books VALUES 

booktown-# 
            (103, '{"The Hobbit", "Kitten, Squared"}');

INSERT 3628400 1

Notice that, in Example 7-18, curly braces are still required to insert a single value into an array. Notice also that the single-quote in the title (first INSERT statement) still requires a backslash preceding it, even though it is surrounded by double-quotes . This is because the array constant itself is parsed as if it were one long string constant, and subsequently interpreted as an array based on the context of its target column.

The insertion of values into a multidimensional array requires a pair of curly braces for each array; an array of arrays must therefore itself be bound in curly braces, while each of its member arrays should be separated by one another with commas. Example 7-19 inserts a single row containing a multidimensional array constant into the favorite_authors table, created in Example 7-17.

booktown=# 
INSERT INTO favorite_authors

booktown-# 
            VALUES (102,

booktown(# 
            '{{"J.R.R. Tolkien", "The Silmarillion"},

booktown'# 
              {"Charles Dickens", "Great Expectations"},

booktown'# 
              {"Ariel Denham", "Attic Lives"}}');

INSERT 3727961 1

Notice that the inserted multidimensional array in Example 7-19 contains three text arrays, which each have two members. There is no systematic relationship between these arrays, though the implied relationship from the context is that the first members of each array are authors corresponding to the second members of each array, which are the favorite titles from the associated author.