<!-- doc/src/sgml/array.sgml -->

<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 or user-defined base type, enum type, composite type, range type,
  or domain can be created.
 </para>

 <sect2 id="arrays-declaration">
  <title>Declaration of Array Types</title>

  <indexterm>
   <primary>array</primary>
   <secondary>declaration</secondary>
  </indexterm>

 <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>[]</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 ignores any supplied array size
  limits, i.e., the behavior is the same as for arrays of unspecified
  length.
 </para>

 <para>
  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 the array size or number of dimensions in
  <command>CREATE TABLE</command> is simply documentation; it does not
  affect run-time behavior.
 </para>

 <para>
  An alternative syntax, which conforms to the SQL standard by using
  the keyword <literal>ARRAY</literal>, can be used for one-dimensional arrays.
  <structfield>pay_by_quarter</structfield> could have been defined
  as:
<programlisting>
    pay_by_quarter  integer ARRAY[4],
</programlisting>
  Or, if no array size is to be specified:
<programlisting>
    pay_by_quarter  integer ARRAY,
</programlisting>
  As before, however, <productname>PostgreSQL</productname> does not enforce the
  size restriction in any case.
 </para>
 </sect2>

 <sect2 id="arrays-input">
  <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 can 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.
   Among the standard data types provided in the
   <productname>PostgreSQL</productname> distribution, all use a comma
   (<literal>,</literal>), except for type <type>box</type> which uses a semicolon
   (<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}}'