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

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.

Example 7-18. Inserting array constants

INSERT INTO favorite_books VALUES 

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

INSERT 3628399 1
INSERT INTO favorite_books VALUES 

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

Example 7-19. Inserting values into multidimensional arrays

INSERT INTO favorite_authors

            VALUES (102,

            '{{"J.R.R. Tolkien", "The Silmarillion"},

              {"Charles Dickens", "Great Expectations"},

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

Databases - Practical PostgreSQL
Previous Page Home Next Page

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