Arrays in Fields

I l @ ve RuBoard

One of the nice features that PostgreSQL supports is the concept of fields that can hold arrays. This enables multiple values of the same data type to be stored in a single field.

To insert arrays into a field, the field should be marked as holding arrays during table creation. After a field has been designated to hold arrays, data can be inserted, selected, or updated into the array by referring to the specific array element in the designated table.

Creating an Array

For example, let's create a table named students that has a four-element array field to hold test scores:

 CREATE TABLE students       (name char(20), testscore int[4]); 

Notice that, at table creation time, a field is designated as being able to support arrays by including brackets [] next to the data-type definition.

Utilizing Array Fields

When inserting, updating, or selecting data, the specific array element can be chosen by referencing it explicitly. PostgreSQL begins numbering array elements at 1. Therefore, the first element in the array testscore would be referenced as testscore[1] .

Now let's insert some sample data into your table students . Notice how you refer to the specific element you want to address by using braces {} :

 INSERT INTO students (name, testscore)        VALUES ('Bill', '{90,0,0,0}');  INSERT INTO students (name, testscore)        VALUES ('Sam', '{86,0,0,0}');  INSERT INTO students (name, testscore)        VALUES ('Pam', '{95,0,0,0}'); 

Notice that the array fields are referenced by using braces {} ; specifically , the array elements are referenced where you wanted the data to be inserted. Likewise, the same strategy is used when updating the row is done.

In fact, either the entire array can be replaced or just the specific element:

 UPDATE students SET testscore='{95,86,0,0}' WHERE name='Pam'; 

Or you can just update the desired element:

 UPDATE students SET testscore[3]=98 WHERE name='Pam'; 

Selecting specific elements can be done in the same way. For instance, to select all students who scored higher than an 85 on the first three tests, you would use the following:

 SELECT * FROM students WHERE testscore[1]>85       AND testscore[2]>85 AND testscore[3]>85; 

Multidimensional Arrays

PostgreSQL also enables multidimensional arrays to be created and used. Suppose you wanted to track each student's test for each half of the school year. In this case, it would be easy to create a multidimensional array to hold that information:

 CREATE TABLE students (name char(20), testscore int[4][4]); 

You could then insert and access that information as before (notice the use of the double braces):

 INSERT INTO students VALUES ('Bill',        '{{75,85,99,68},{88,91,77,87}}'); 

Selection can then be made for the specific element in the specific multidimensional array. For instance, to see who scored greater than a 90 on the third exam in the second half of school:

 SELECT * FROM students WHERE testscore[2][3]>90; 

Extending Arrays

One caveat (or benefit) of the PostgreSQL array structure is that element sizes within an array can be expanded dynamically. Although you might explicitly specify a maximum array size during the table creation, this size can be altered by using the UPDATE command.

For instance, here is an example of a table created with an array. The table then utilizes the UPDATE command to extend the size of the array:

 CREATE TABLE students (name char(20), testscore int[3]);  INSERT INTO students VALUES ('Bill', '{96,84,98}');  SELECT * FROM students;  Name      testscore  ------------------- Bill      {96,84,98}  UPDATE students SET testscore='{96,84,98,100}';  SELECT * FROM students;  Name      testscore  ------------------- Bill      {96,84,98,100} 

Although this can be a useful feature, it can also be problematic unless used carefully . It would be possible to end up with different rows that each have a different number of array elements.

One useful function for dealing with arrays is the ARRAY_DIMS function. This function returns the current number of elements in an array. Refer to the ARRAY_DIMS function in Chapter 4, "PostgreSQL Functions."

I l @ ve RuBoard