array.sgml

PostgreSQL7.4.6 for Linux

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.32.2.1 2003/11/04 09:45:29 petere Exp $ -->

<sect1 id="arrays">
 <title>Arrays</title>

 <indexterm>
  <primary>array</primary>
 </indexterm>

 <para>
  <productname>PostgreSQL</productname> allows columns of a table to be
  defined as variable-length multidimensional arrays. Arrays of any
  built-in type or user-defined type can be created.
 </para>

 <sect2>
  <title>Declaration of Array Types</title>

 <para>
  To illustrate the use of array types, we create this table:
<programlisting>
CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);
</programlisting>
  As shown, an array data type is named by appending square brackets
  (<literal>[]</>) to the data type name of the array elements.  The
  above command will create a table named
  <structname>sal_emp</structname> with a column of type
  <type>text</type> (<structfield>name</structfield>), a
  one-dimensional array of type <type>integer</type>
  (<structfield>pay_by_quarter</structfield>), which represents the
  employee's salary by quarter, and a two-dimensional array of
  <type>text</type> (<structfield>schedule</structfield>), which
  represents the employee's weekly schedule.
 </para>

 <para>
  The syntax for <command>CREATE TABLE</command> allows the exact size of
  arrays to be specified, for example:

<programlisting>
CREATE TABLE tictactoe (
    squares   integer[3][3]
);
</programlisting>

  However, the current implementation does not enforce the array size
  limits --- the behavior is the same as for arrays of unspecified
  length.
 </para>

 <para>
  Actually, the current implementation does not enforce the declared
  number of dimensions either.  Arrays of a particular element type are
  all considered to be of the same type, regardless of size or number
  of dimensions.  So, declaring number of dimensions or sizes in
  <command>CREATE TABLE</command> is simply documentation, it does not
  affect runtime behavior.
 </para>

 <para>
  An alternative, SQL99-standard syntax may be used for one-dimensional arrays.
  <structfield>pay_by_quarter</structfield> could have been defined as:
<programlisting>
    pay_by_quarter  integer ARRAY[4],
</programlisting>
  This syntax requires an integer constant to denote the array size.
  As before, however, <productname>PostgreSQL</> does not enforce the
  size restriction.
 </para>
 </sect2>

 <sect2>
  <title>Array Value Input</title>

  <indexterm>
   <primary>array</primary>
   <secondary>constant</secondary>
  </indexterm>

  <para>
   To write an array value as a literal constant, enclose the element
   values within curly braces and separate them by commas.  (If you
   know C, this is not unlike the C syntax for initializing
   structures.)  You may put double quotes around any element value,
   and must do so if it contains commas or curly braces.  (More
   details appear below.)  Thus, the general format of an array
   constant is the following:
<synopsis>
'{ <replaceable>val1</replaceable> <replaceable>delim</replaceable> <replaceable>val2</replaceable> <replaceable>delim</replaceable> ... }'
</synopsis>
   where <replaceable>delim</replaceable> is the delimiter character
   for the type, as recorded in its <literal>pg_type</literal> entry.
   (For all built-in types, this is the comma character
   <quote><literal>,</literal></>.)  Each
   <replaceable>val</replaceable> is either a constant of the array
   element type, or a subarray.  An example of an array constant is
<programlisting>
'{{1,2,3},{4,5,6},{7,8,9}}'
</programlisting>
   This constant is a two-dimensional, 3-by-3 array consisting of
   three subarrays of integers.
  </para>

  <para>
   (These kinds of array constants are actually only a special case of
   the generic type constants discussed in <xref
   linkend="sql-syntax-constants-generic">.  The constant is initially
   treated as a string and passed to the array input conversion
   routine.  An explicit type specification might be necessary.)
  </para>

  <para>
   Now we can show some <command>INSERT</command> statements.

<programlisting>
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"talk", "consult"}, {"meeting"}}');
</programlisting>
  </para>

  <para>
   A limitation of the present array implementation is that individual
   elements of an array cannot be SQL null values.  The entire array
   can be set to null, but you can't have an array with some elements
   null and some not.
  </para>

 <para>
  This can lead to surprising results. For example, the result of the
  previous two inserts looks like this:
<programlisting>
SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |      schedule
-------+---------------------------+--------------------
 Bill  | {10000,10000,10000,10000} | {{meeting},{""}}
 Carol | {20000,25000,25000,25000} | {{talk},{meeting}}
(2 rows)
</programlisting>
  Because the <literal>[2][2]</literal> element of
  <structfield>schedule</structfield> is missing in each of the
  <command>INSERT</command> statements, the <literal>[1][2]</literal>
  element is discarded.
 </para>

 <note>
  <para>
   Fixing this is on the to-do list.
  </para>
 </note>

 <para>
  The <literal>ARRAY</literal> expression syntax may also be used:
<programlisting>
INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['','']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting', '']]);
SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |           schedule
-------+---------------------------+-------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{"",""}}
 Carol | {20000,25000,25000,25000} | {{talk,consult},{meeting,""}}
(2 rows)
</programlisting>
  Note that with this syntax, multidimensional arrays must have matching
  extents for each dimension. A mismatch causes an error report, rather than
  silently discarding values as in the previous case.
  For example:
<programlisting>
INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['talk', 'consult'], ['meeting']]);
ERROR:  multidimensional arrays must have array expressions with matching dimensions
</programlisting>
  Also notice that the array elements are ordinary SQL constants or
  expressions; for instance, string literals are single quoted, instead of
  double quoted as they would be in an array literal.  The <literal>ARRAY</>
  expression syntax is discussed in more detail in <xref
  linkend="sql-syntax-array-constructors">.
 </para>
 </sect2>

 <sect2>
  <title>Accessing Arrays</title>

 <para>
  Now, we can run some queries on the table.
  First, we show how to access a single element of an array at a time.
  This query retrieves the names of the employees whose pay changed in
  the second quarter:
     
<programlisting>
SELECT name FROM sal_emp WHERE pay_by_quarter[1] &lt;&gt; pay_by_quarter[2];

 name
-------
 Carol
(1 row)
</programlisting>

  The array subscript numbers are written within square brackets.
  By default <productname>PostgreSQL</productname> uses the
  one-based numbering convention for arrays, that is,
  an array of <replaceable>n</> elements starts with <literal>array[1]</literal> and
  ends with <literal>array[<replaceable>n</>]</literal>.
 </para>

 <para>
  This query retrieves the third quarter pay of all employees:
     
<programlisting>
SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)
</programlisting>
 </para>

 <para>
  We can also access arbitrary rectangular slices of an array, or
  subarrays.  An array slice is denoted by writing
  <literal><replaceable>lower-bound</replaceable>:<replaceable>upper-bound</replaceable></literal>
  for one or more array dimensions.  For example, this query retrieves the first
  item on Bill's schedule for the first two days of the week:
     
<programlisting>
SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

      schedule
--------------------
 {{meeting},{""}}
(1 row)
</programlisting>

  We could also have written

<programlisting>
SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill';
</programlisting>

  with the same result.  An array subscripting operation is always taken to
  represent an array slice if any of the subscripts are written in the form
  <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>.
  A lower bound of 1 is assumed for any subscript where only one value
  is specified, as in this example:
<programlisting>
SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
         schedule
---------------------------
 {{meeting,lunch},{"",""}}
(1 row)
</programlisting>
 </para>

 <para>
  The current dimensions of any array value can be retrieved with the
  <function>array_dims</function> function:

<programlisting>
SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:1]
(1 row)
</programlisting>

  <function>array_dims</function> produces a <type>text</type> result,
  which is convenient for people to read but perhaps not so convenient
  for programs.  Dimensions can also be retrieved with
  <function>array_upper</function> and <function>array_lower</function>,
  which return the upper and lower bound of a
  specified array dimension, respectively.

<programlisting>
SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_upper
-------------
           2
(1 row)