8.10 Working with Arrays

Arrays are a core feature of PostgreSQL. Many people admire PostgreSQL because of this feature because it is a comfortable way to reduce the number of relations in your data structure as well as to store multiple values in just one cell.

8.10.1 One-Dimensional Arrays

To define a column as an array, brackets have to be added to the data type:

 phpbook=# CREATE TABLE parent (name_mother text, name_children text[]); CREATE 

If you have a look at the data structure, you will see that the second column has been defined as an array:

 phpbook=# \d parent            Table "parent"     Column     |  Type  | Modifiers ---------------+--------+-----------  name_mother   | text   |  name_children | text[] | 

To insert a record into the table, you can use a special syntax:

 phpbook=# INSERT INTO parent VALUES ('Andrea', '{"Paul", "Lisa"}'); INSERT 26021 1 

Curly brackets tell PostgreSQL that an array is being passed to the database. The string values in the array must be surrounded by double quotes. Let's query the table:

 phpbook=# SELECT * FROM parent;  name_mother |  name_children -------------+-----------------  Andrea      | {"Paul","Lisa"} (1 row) 

Just as in any other programming language, values can be accessed by using an index:

 phpbook=# SELECT name_mother, name_children[0] FROM parent;  name_mother | name_children -------------+---------------  Andrea      | (1 row) 

As you can see, the element number zero is empty because PostgreSQL starts to count the values at index number one:

 phpbook=# SELECT name_mother, name_children[1] FROM parent;  name_mother | name_children -------------+---------------  Andrea      | Paul (1 row) 

Various fields of an array can be displayed in just one column:

 phpbook=# SELECT name_mother, name_children[1], name_children[2] FROM parent;  name_mother | name_children | name_children -------------+---------------+---------------  Andrea      | Paul          | Lisa 

This is comfortable because you don't have to perform join operations everything can be found in just one table. Some of you might think that arrays violate Codd's Normal Forms that's correct, but arrays are just an extra feature and need not be used. It is up to you to decide whether arrays are useful for you.

8.10.2 Limited Arrays

The table you have just dealt with contains an unlimited array, which means that many values can be inserted into it. If you want to limit an array, that can also be done. Let's drop the table you have just created and generate a new one:

 phpbook=# DROP TABLE parent; DROP phpbook=# CREATE TABLE parent (name_mother text, name_children text[2]); CREATE 

The description of the table is exactly the same as you have seen before:

 phpbook=# \d parent            Table "parent"     Column     |  Type  | Modifiers ---------------+--------+-----------  name_mother   | text   |  name_children | text[] | 

Although the size of the column is limited to two records, you can add as many values to the columns as you want because PostgreSQL 7.2 treats limited and unlimited columns the same way:

 phpbook=# INSERT INTO parent VALUES ('Andrea', '{"Paul", "Lisa"}'); INSERT 26027 1 phpbook=# INSERT INTO parent VALUES ('Pauline', '{"Charles", "Pat", "Jim"}'); INSERT 26028 1 

Pauline's three children are listed in the result:

 phpbook=# SELECT * FROM parent;  name_mother |      name_children -------------+-------------------------  Andrea      | {"Paul","Lisa"}  Pauline     | {"Charles","Pat","Jim"} (2 rows) 

8.10.3 Multidimensional Arrays

PostgreSQL is capable of handling multidimensional arrays. Take a look at an example:

 phpbook=# DROP TABLE parent; DROP phpbook=# CREATE TABLE parent (name_mother text, name_gender_children text[][]); CREATE 

A two-dimensional array has been generated:

 phpbook=# \d parent               Table "parent"         Column        |  Type  | Modifiers ----------------------+--------+-----------  name_mother          | text   |  name_gender_children | text[] | 

In the next step values can be added to the array. The syntax for multidimensional arrays does not differ from the one you have just dealt with significantly:

 phpbook=# INSERT INTO parent VALUES ('Andrea', '{{"Paul", "Lisa"}, {"male", "female"}}'); INSERT 26034 1 

The values are displayed in the table the same way:

 phpbook=# SELECT * FROM parent;  name_mother |        name_gender_children -------------+-------------------------------------  Andrea      | {{"Paul","Lisa"},{"male","female"}} (1 row) 

To retrieve the data, both dimensions have to be defined:

 phpbook=# SELECT name_gender_children[1][1], name_gender_children[2][1] FROM parent;  name_gender_children | name_gender_children ----------------------+----------------------  Paul                 | male (1 row) 

In some cases it is necessary to find the size of an array. Therefore PostgreSQL provides a function called array_dims:

 phpbook=# SELECT array_dims(name_gender_children) FROM parent;  array_dims ------------  [1:2][1:2] (1 row) 

In this scenario each dimension contains two elements. The information retrieved from the database can be used to select entire ranges from an array, as shown in the next listing:

 phpbook=# SELECT name_mother, name_gender_children[1][1:2] FROM parent;  name_mother | name_gender_children -------------+----------------------  Andrea      | {{"Paul","Lisa"}} (1 row) 

Both elements of the second axis are displayed because a range has been defined.

In the next step you will try to remove elements from an array. Let's try to set a cell to NULL:

 phpbook=# UPDATE parent SET name_gender_children[1][1] = NULL; UPDATE 1 

Although one row has been updated, no changes have been made the array is still the same:

 phpbook=# SELECT name_mother, name_gender_children[1][1:2] FROM parent;  name_mother | name_gender_children -------------+----------------------  Andrea      | {{"Paul","Lisa"}} (1 row) 

One way to get rid of an element is to assign an empty string to the cell. This is not a good solution, but in some cases it will help you:

 phpbook=# UPDATE parent SET name_gender_children[1][1] = ''; UPDATE 1 

The element is still in the array but it is empty:

 phpbook=# SELECT name_mother, name_gender_children FROM parent;  name_mother |      name_gender_children -------------+---------------------------------  Andrea      | {{"","Lisa"},{"male","female"}} (1 row) 

A better solution would be to write a PL/pgSQL or PL/Perl function that parses cells containing the array and updates the entire cell. We have decided not to include a function like that because it would be far beyond the scope of this book.