Arrays

One of the unique features of PostgreSQL is the fact that you can define a column to be an array. Most commercial database systems require that a single column within a given row can hold no more than one value. With PostgreSQL, you aren't bound by that ruleyou can create columns that store multiple values (of the same data type).

The customers table defined in Chapter 1 contained a single balance column. What change would you have to make to the database if you wanted to store a month-by-month balance for each customer, going back at most 12 months? One alternative would be to create a separate table to store monthly balances. The primary key of the cust_balance might be composed of the customer_id and the month number (either 0-11 or 1-12, whichever you found more convenient)[12]. This would certainly work, but in PostgreSQL, it's not the only choice.

[12] The relationship between the customers table and the cust_balance is called a parent/child relationship. In this case, the customers table is the parent and cust_balance is the child. The primary key of a child table is composed of the parent key plus a uniquifier (that is, a value, such as the month number, that provides a unique identifier within a group of related children).

You know that there are never more than 12 months in a year and that there are never fewer than 12 months in a year. Parent/child relationships are perfect when the parent has a variable number of children, but they aren't always the most convenient choice when the number of child records is fixed.

Instead, you could store all 12 monthly balance values inside the customers table. Here is how you might create the customers table using an array to store the monthly balances:

CREATE TABLE customers (
 customer_id INTEGER UNIQUE,
 customer_name VARCHAR(50),
 phone CHAR(8),
 birth_date DATE,
 balance DECIMAL(7,2),
 monthly_balances DECIMAL(7,2)[12]
);

Notice that I have added a new column named monthly_balances this is an array of 12 DECIMAL values. I'll show you how to put values into an array in a moment.

You can define an array of any data type: the built-in types, user-defined types, even other arrays. When you create an array of arrays, you are actually creating a multidimensional array. For example, if we wanted to store month-by-month balances for the three previous years, I could have created the monthly_balances field as

monthly_balances DECIMAL(7,2)[3][12]

This would give you three arrays of 12-element arrays.

There is no limit to the number of members in an array. There is also no limit to the number of dimensions in a multidimensional array.

Now, let's talk about inserting and updating array values. When you want to insert a new row into the customers table, you provide values for each member in the monthly_balances array as follows:

INSERT INTO customers
(
customer_id, customer_name, phone, birth_date, balance, monthly_balances
)
VALUES
(
 8,
 'Wink Wankel',
 '555-1000',
 '1988-12-25',
 0.00,
 '{1,2,3,4,5,6,7,8,9,10,11,12}'
);

To INSERT values into an array, you enclose all the array elements in single quotes and braces ({}) and separate multiple elements with a comma. Starting with PostgreSQL version 7.4, you can use an alternate form to write an array value. To express the same array value in constructor syntax form, you would write:

ARRAY[1,2,3,4,5,6,7,8,9,10,11,12]

There are two advantages to the new array constructor syntax. First, the meaning is a bit more obvious when you're looking at a piece of unfamiliar SQL code. Second, if you write an array value expressed '{ element 1 , element 2, ...} ' form, you have to double up the quotes if the array contains string values. In array constructor form, you don't have to do that. The following array values are equivalent:

'{ ''Panky, Henry'', ''Rubin, William'', ''Grumby, Jonas'' }'
ARRAY[ 'Panky, Henry', 'Rubin, William', 'Grumby, Jonas' ]

Inserting values into a multidimensional array is treated as if you were inserting an array of arrays. For example, if you had a table defined as

CREATE TABLE arr
(
 pkey serial,
 val int[2][3]
);

you would INSERT a row as

INSERT INTO arr( val ) VALUES( '{ {1,2,3}, {4,5,6} }' );

Or, to write the same array value in constructor form:

INSERT INTO arr( val ) VALUES( ARRAY[ [1,2,3], [4,5,6] ] );

Looking back at the customers table now; if you SELECT the row that you INSERT ed, you'll see:

movies=# x
Expanded display is on

movies=# SELECT
movies-# customer_name, monthly_balances
movies-# FROM customers
movies-# WHERE customer_id = 8;
-[ RECORD 1 ]----+------------------------------------

customer_name | Wink Wankel

monthly_balances | {1.00,2,3,4,5,6,7,8,9,10,11,12.00}

To make this example a little more readable in book form, I have used psql 's x command to rearrange the display format here. I have also edited out some of the trailing zeroes in the monthly_balances column.

You can retrieve specific elements within an array:

movies=# SELECT
movies-# customer_name, monthly_balances[3]
movies-# FROM customers
movies-# WHERE customer_id = 8;
 customer_name | monthly_balances
---------------+------------------
 Wink Wankel | 3.00
(1 row)

Or you can ask for a range[13] of array elements:

[13] The PostgreSQL documentation refers to a contiguous range of array elements as a slice.

movies=# SELECT
movies-# customer_name, monthly_balances[1:3]
movies-# FROM customers
movies-# WHERE customer_id = 8;
 customer_name | monthly_balances
---------------+------------------------
 Wink Wankel | {"1.00","2.00","3.00"}
(1 row)

The index for an array starts at 1 by default. I'll show you how to change the range of an index in a moment.

You can use an array element in any situation where you can use a value of the same data type. For example, you can use an array element in a WHERE clause:

movies=# SELECT
movies-# customer_name, monthly_balances[1:3]
movies-# FROM customers
movies-# WHERE monthly_balances[1] > 0;
 customer_name | monthly_balances
---------------+------------------------
 Wink Wankel | {"1.00","2.00","3.00"}
(1 row)

There are three ways to UPDATE an array. If you want to UPDATE all elements in an array, simply SET the array to a new value:

movies=# UPDATE customers SET
movies-# monthly_balances = '{12,11,10,9,8,7,6,5,4,3,1}'
movies-# WHERE customer_id = 8;

If you want to UPDATE a single array element, simply identify the element:

movies=# UPDATE customers SET monthly_balances[1] = 22;

Finally, you can UPDATE a contiguous range of elements:

movies=# UPDATE customers SET monthly_balances[1:3] = '{11,22,33}';

Now, there are a few odd things you should know about arrays in PostgreSQL.

First, the array bounds that you specify when you create a column are optional. I don't just mean that you can omit an array bound when you create a column (although you can), I mean that PostgreSQL won't enforce any limits that you try to impose. For example, you created the monthly_balances column as a 12-element array. PostgreSQL happily lets you put a value into element 13, 14, or 268. The array_dims() function tells the upper and lower bounds of an array value:

movies=# SELECT array_dims( monthly_balances ) FROM customers
movies-# WHERE
movies-# customer_id = 8;

array_dims
------------
 [1:12]

You can increase the size of an array by updating values adjacent to those that already exist[14]. For example, the monthly_balances column for customer 8 (Wink Wankel) contains 12 elements, numbered 1 through 12. You can add new elements at either end of the range (array subscripts can be negative):

[14] The PostgreSQL documentation warns that you can't expand a multidimensional array.

movies=# UPDATE customers SET
movies-# monthly_balances[13] = 13
movies-# WHERE
movies-# customer_id = 8;
UPDATE 1

movies=# SELECT array_dims( monthly_balances ) FROM customers
movies-# WHERE
movies-# customer_id = 8;
 array_dims
------------
 [1:13]

movies=# UPDATE customers SET
movies-# monthly_balances[-1:0] = '{ -1, 0 }'
movies-# WHERE
movies-# customer_id = 8;
UPDATE 1

movies=# SELECT array_dims( monthly_balances ) FROM customers
movies-# WHERE
movies=# customer_id = 8;
array_dims
------------
 [-1:13]

Note that you can expand an array only by updating elements that are directly adjacent to the existing elements. For example, customer number 8 now contains elements -1:13. We can't add an element 15 without first adding element 14:

movies=# UPDATE customers SET
movies-# monthly_balances[15] = 15
movies-# WHERE
movies-# customer_id = 8;
ERROR: Invalid array subscripts

Next, the syntax for inserting or updating array values is a bit misleading. Let's say that you want to insert a new row in your customers table, but you only want to provide a balance for month number 3:

movies=# INSERT INTO customers
movies-# ( customer_id, customer_name, monthly_balances[3] )
movies-# VALUES
movies-# ( 9, 'Samuel Boney', '{300}' );

This appears to work, but there is danger lurking here. Let's go back and retrieve the data that you just inserted:

movies=# SELECT customer_name, monthly_balances[3]
movies-# FROM customers
movies-# WHERE
movies-# customer_id = 9;
 customer_name | monthly_balances
---------------+------------------
 Samuel Boney |

Where'd the data go? If you SELECT all array elements, the data is still there:

movies=# SELECT customer_name, monthly_balances
movies-# FROM customers
movies-# WHERE
movies-# customer_id = 9;
 customer_name | monthly_balances
---------------+------------------
 Samuel Boney | {"300"}

The array_dims() function gives you a pretty good hint:

movies=# SELECT array_dims( monthly_balances ) FROM customers
movies-# WHERE
movies-# customer_id = 9;

array_dims
------------
 [1:1]

According to array_dims(), the high and low subscript values are both 1. You explicitly INSERT ed the value 300 into array element 3, but PostgreSQL (silently) decided to place it into element 1 anyway. This seems a bit mysterious to me, but that's how it works.

The final oddity concerns how PostgreSQL handles NULL values and arrays. An array can be NULL, but an individual element cannotyou can't have an array in which some elements are NULL and others are not. Furthermore, PostgreSQL silently ignores an attempt to UPDATE an array member to NULL :

movies=# SELECT customer_name, monthly_balances
movies-# FROM
movies-# customers
movies-# WHERE
movies-# customer_id = 8;
-[ RECORD 1 ]----+------------------------------------
id | 8
customer_name | Wink Wankel
phone | 555-1000
birth_date | 1988-12-25
monthly_balances | {1.00,2,3,4,5,6,7,8,9,10,11,12.00}

movies=# UPDATE customers SET
movies-# monthly_balances[1] = NULL
movies-# WHERE
movies-# customer_id = 8;
UPDATE 1

You won't get any error messages when you try to change an array element to NULL, but a SELECT statement will show that the UPDATE had no effect:

movies=# SELECT customer_name, monthly_balances
movies-# FROM
movies-# customers
movies-# WHERE
movies-# customer_id = 8;
-[ RECORD 1 ]----+------------------------------------
id | 8
customer_name | Wink Wankel
phone | 555-1000
birth_date | 1988-12-25
monthly_balances | {1.00,2,3,4,5,6,7,8,9,10,11,12.00}

If you keep these three oddities in mind, arrays can be very useful. Remember, though, that an array is not a substitute for a child table. You should use an array only when the number of elements is fixed by some real-world constraint (12 months per year, 7 days per week, and so on).