To help visualize this array, the following table shows the values and the index number of the array index location.

Table 5-8. Fixed Week Array

Index Value
[1] Mon
[2] Tues
[3] Weds
[4] Thurs
[5] Fri
[6] Sat
[7] Sun

You can use several different queries to access, modify, and update the week table. To obtain the fifth day of the week, specify the 5th array position or index:

Example 5-14. Selecting an Array Position

  SELECT days_of_week[5] FROM week;
   

The output is:

 days_of_week
-----------
 Fri
(1 row)
   

Unlike many programming languages, these array indices start at the value of 1 instead of 0. These arrays also end at n number of array items, not n-1. When accessing an array, you don't need to minus one from the index number that you want to retrieve.

Note Max Size
 

Variable and fixed length arrays do not have a maximum size limit.

Multi-dimensional arrays work differently from a one-dimensional array. For example, the customer table contains customer identification numbers, first names, and last names. Each customer has a different phone number to contact. They have a home phone, a work phone, and sometimes, a cell phone number. They also have a different work address from their home address. Each address contains a street, city, state, and zip code.

Create an array for customer phone numbers and addresses using the existing customer table. This table is already in the database. Therefore, we can use the ALTER TABLE command instead of CREATE TABLE to add a new array column. Let's add a one-dimensional array for each customer phone number by using a single bracket pair [] and add a two-dimensional array to store their addresses by using two bracket pairs [][]. The first dimension of the address array stores their home address and the second stores their work address:

Example 5-15. Adding Multi-Dimensional Arrays to a Table

    ALTER TABLE customer 
          ADD COLUMN phonenum[],
                     address text[][];
   

After creating this table, you can view the table structure using the \d tablename syntax. When you view this table, it shows the following:

        Table "customer"
 Attribute |  Type   | Modifier
-----------+---------+----------
 cust_id   | integer |
 lastname  | text    |
 firstname | text    |
 phonenum  | text[]  |
 address   | text[]  |
   

Notice that both the phone number column and the address column is of type array. But the address column should have a pair of brackets to show it is a two-dimensional array. If a user who was unfamiliar with this table were to look at this chart, they would not know that this is a two-dimensional array, unless they looked at the data stored in the database. If there were no data values inserted yet, then it is difficult to know the number of dimensions this array contains. Hopefully, a later version will correct this problem and display two sets of square brackets for a two-dimensional array, three sets of brackets for a three-dimensional array, and so on.

To insert values into these arrays, we can perform an update to each customer row. Remember that these customers already exist in the table, we have just added a column of arrays and now we are populating only those columns and keeping the current information stored about the customers. The update should look like this:

Example 5-16. Updating Multi-Dimensional Arrays

  UPDATE customer SET phonenum = 
          '{"503-788-3413","503-253-4592", "503-532-7893"}',
          '{
           {"323 SW Gnome Ave","Portland","Oregon","39823"},
           {"12 NW Salmon St","Beaverton","Oregon","74283"}
           }' 
            WHERE firstname = 'Dr.John';
   

After updating all of the customers in that table, we can display it using the SELECT statement. This displays all values in the address and phone number arrays:

  SELECT phonenum, address
    FROM customer;
   

To better visualize this large array, the information contained in the address and phone number columns for Dr.John are shown in the two charts below:

Table 5-9. One-Dimensional Phone Number Array

Index Value
[1] 503-788-3413
[2] 503-253-4592
[3] 503-532-7893

Table 5-10. Two-Dimensional Address Array

Home Work
Index Value Index Value
[1] 323 SW Gnome Ave [1] 12 NW Salmon St
[2] Portland [2] Beaverton
[3] Oregon [3] Oregon
[4] 39823 [4] 74283

If you already have data in the array table and you want to update only a specific value, then use the array position number to specify which value you want change. With the single arrayed phone numbers, you can change the work phone number, which is the second index number in the array, by using the following statement:

  UPDATE customer 
     SET phonenum[2] = "503-677-3847" 
   WHERE firstname = 'Dr.John';
   

To find out which city a customer works in and which city they live in, use the array index. This array location have to be the second position in each array because we inserted the values starting with address, city, state, then zip code. Remember that address is a two-dimensional array. To access the home address and the work address at one time, use the following:

Example 5-17. Viewing Multi-Dimensional Arrays

   SELECT address[1][2] as home,
          address[2][2] as work 
     FROM customer;
   

The result from this query is:

   home    |     work
-----------+---------------
 Tampa Bay | Tampa Bay
 San Jose  | Santa Barbara
 Portland  | Beaverton
(3 rows)
   

If you need to update a two-dimensional array, you can specify the array index number within two bracket pairs. Let us suppose that Dr.John's home address changed, but it is still within the same state. His work address will remain the same. We only need to change his street address and zip code.

Example 5-18. Updating Multi-Dimensional Arrays

  UPDATE customer 
     SET address [1][1]= '423 SE Division St', 
         address [1][4]= '97234'
   WHERE firstname = 'Dr.John';          
   

You may have noticed that the syntax above does not use curly braces when it specifies the data value to be inserted. This is because the system already knows that we are accessing an array. We are giving the exact coordinate of the array when we put a number inside the square brackets.

If you use a pair of single quotes around curly braces, and inside of that, a pair of double quotes, then it will take the curly braces and quotes as a literal. Therefore, when you update an array at a particular array position, it is not necessary to use the curly braces or double quotes. However, if you perform an update to an existing array with no values yet, then you will need to use the curly braces and double quotes like we did in our example when we first added the array values.

If you want to change or access a consecutive number of indices in an array, use the format:

  [

beg_indexNum

:

end_indexNum

]

The beg_indexNum field is the number in the index that you want to start at. The end_indexNum field is the ending index number that you want to end at. For instance, if you wanted to display the work and cell phone numbers in the array, then you will need to access the second through the third index in the array. Use the following:

Example 5-19. Viewing Consecutive Array Positions

  SELECT firstname, phonenum[2:3] 
    FROM customer;   
   

The result shows that only Dr.John has a cell phone number, while the other two customers only have a work phone number:

 firstname |        phonenum
-----------+-------------------------
 Mr.Jeff   | {"536-746-0428"}
 Mrs.Helen | {"714-323-9384"}
 Dr.John   | {"503-253-4592","503-532-7893"}
(3 rows)
   

If you wanted to access consecutive indices in a multi-dimension array, then you can first specify which array you ant to extract the data from, then use a colon separated array index to specify from where the search should begin and end. Using the two-dimensional address array, the example below searches for the city, and state of the customer's home address:

Example 5-20. Viewing Consecutive Array Positions in a Multi-Dimensional Array

  SELECT firstname, address[1][2:3] FROM customer;
   

The result of this query is:

 firstname |           address
-----------+-----------------------------
 Mr.Jeff   | {{"Tampa Bay","Florida"}}
 Mrs.Helen | {{"San Jose","California"}}
 Dr.John   | {{"Portland","Oregon"}}
(3 rows)
   

Caution Limit Array Usage
 

We recommend that you avoid using arrays on fields where searches will be performed. The array causes the search to slow down dramatically because the system needs to search and check each array index, instead of performing a sequential search. The more dimensions an array has, the longer the time it takes to search through the entire array.