Arrays

Arrays allow a column to store several simple data values. You can store one-dimensional arrays, two-dimensional arrays, or arrays with any number of dimensions.

You create an array column in the same way as an ordinary column, except that you use brackets to specify the dimensions of the array. The number of dimensions and size of each dimension are for documentation purposes only. Values that do not match the dimensions specified at the time of column creation are not rejected.

Figure  creates a table with one-, two-, and three-dimensional INTEGER columns.  

        test=> CREATE TABLE array_test (
        test(>                          col1  INTEGER[5],
        test(>                          col2  INTEGER[][],
        test(>                          col3  INTEGER[2][2][]
        test(> );
        CREATE  

The first and last columns have sizes specified. The first column is a one-dimensional array, also called a list or vector. Values inserted into that column have an appearance like {3,10,9,32,24} or {20,8,9,1,4}. That is, each value is a list of integers, surrounded by curly braces. The second column, col2, is a two-dimensional array. Typical values for this column are {{2,9,3},{4,3,5}} or {{18,6},{32,5}}. Notice the double braces. The outer brace surrounds two one-dimensional arrays. You can think of this structure as a matrix, with the first one-dimensional array representing the first row of the array, and the second representing the second row of the array. Commas separate the individual elements as well as each pair of braces. The third column of the array_test table is a three-dimensional array, holding values like {{{3,1},{1,9}},{{4,5},{8,2}}}. This three-dimensional matrix is made up of two 22 matrices. Arrays of any size can be constructed.

Figure  shows a query inserting values into array_test plus several queries selecting data from this table.  

        test=> INSERT INTO array_test VALUES (
        test(>                                '{1,2,3,4,5}',
        test(>                                '{{1,2},{3,4}}',
        test(>                                '{{{1,2},{3,4}},{{5,6}, {7,8}}}' 
        test(> );
        INSERT 52694 1
        test=> SELECT * FROM array_test;
            col1     |     col2      |             col3              
        -------------+---------------+-------------------------------
         {1,2,3,4,5} | {{1,2},{3,4}} | {{{1,2},{3,4}},{{5,6},{7,8}}}
        (1 row) 
         
        test=> SELECT col1[4] FROM array_test;
         col1 
        ------
            4
        (1 row) 
         
        test=> SELECT col2[2][1] FROM array_test;
         col2 
        ------
            3
        (1 row) 
         
        test=> SELECT col3[1][2][2] FROM array_test;
         col3 
        ------
            4
        (1 row)  

Brackets are used to access individual array elements.

Any data type can be used as an array. If you need to frequently access or update individual elements of the array, use separate columns or tables rather than arrays.