A base type array uses the pre-defined data types to create the array. While a user-defined array uses types created by the user. Here are a few rules to remember when creating an array:

  • To create an array, use the CREATE TABLE command. To add an array column to an existing table, use the ALTER TABLE command.

  • When listing the column names and their data types, use a set of brackets to specify that this is an array.

  • For each dimension you want the array to have, use a set of empty brackets for a variable length array.

  • To create a fixed-length array, specify inside the brackets the maximum size you want the array to have.

The syntax to create an array is:

  CREATE TABLE table
               (
               colname1 type, 
               colname2 type[], 
               colname3 type[][],...
                );
   

The first column named is created as a normal column. The second column specified is created as a variable length one-dimensional array. The third column is a two-dimensional array with a variable length.

First, create a simple fixed array for the 7 days in a week:

Example 5-12. Creating a Fixed Array

    CREATE TABLE week (days_of_week text[7]);  
   

This array represents a regular work week which starts on Monday. It also includes Sat and Sun. To insert values into this table, use the following:

Example 5-13. Inserting Values into a Fixed Array

  INSERT INTO week 
       VALUES ('{"Mon", "Tues", "Weds", 
                 "Thurs", "Fri", "Sat", "Sun"}');
   

Notice that when inserting values, you must use:

  • Single quotes inside the parenthesis that list values to insert. To list more than one array, use a set of single quotes for each array. For example, if you had two arrays you want to insert values into, then you will need to have two sets of single quotes.

  • Curly braces inside those single quotes for each array you want to insert values into. For instance, if you had two arrays in that same table, then you need to have two sets of curly braces.

  • List values inside the curly braces, separated by commas. If the data type is text, then you are required to list the values inside double quotes, with each quoted value separated by commas.

The database displays this table with the quotes and curly braces around the values for the days_of_week column:

 
                     days_of_week
-------------------------------------------------
 {"Mon","Tues","Weds","Thurs","Fri","Sat","Sun"}
(1 row)