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 rule ”you 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.

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} }' ); 

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

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 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 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 one 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 cannot ”you 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).