Home Explore Blog CI



postgresql

9th chunk of `doc/src/sgml/array.sgml`
9d001aebdca97761cfee0ccd78842596f5ed915b32a62d050000000100000e20
 must be written between adjacent curly-braced entities of the same level.
  </para>

  <para>
   The array output routine will put double quotes around element values
   if they are empty strings, contain curly braces, delimiter characters,
   double quotes, backslashes, or white space, or match the word
   <literal>NULL</literal>.  Double quotes and backslashes
   embedded in element values will be backslash-escaped.  For numeric
   data types it is safe to assume that double quotes will never appear, but
   for textual data types one should be prepared to cope with either the presence
   or absence of quotes.
  </para>

  <para>
   By default, the lower bound index value of an array's dimensions is
   set to one.  To represent arrays with other lower bounds, the array
   subscript ranges can be specified explicitly before writing the
   array contents.
   This decoration consists of square brackets (<literal>[]</literal>)
   around each array dimension's lower and upper bounds, with
   a colon (<literal>:</literal>) delimiter character in between. The
   array dimension decoration is followed by an equal sign (<literal>=</literal>).
   For example:
<programlisting>
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)
</programlisting>
   The array output routine will include explicit dimensions in its result
   only when there are one or more lower bounds different from one.
  </para>

  <para>
   If the value written for an element is <literal>NULL</literal> (in any case
   variant), the element is taken to be NULL.  The presence of any quotes
   or backslashes disables this and allows the literal string value
   <quote>NULL</quote> to be entered.  Also, for backward compatibility with
   pre-8.2 versions of <productname>PostgreSQL</productname>, the <xref
   linkend="guc-array-nulls"/> configuration parameter can be turned
   <literal>off</literal> to suppress recognition of <literal>NULL</literal> as a NULL.
  </para>

  <para>
   As shown previously, when writing an array value you can use double
   quotes around any individual array element. You <emphasis>must</emphasis> do so
   if the element value would otherwise confuse the array-value parser.
   For example, elements containing curly braces, commas (or the data type's
   delimiter character), double quotes, backslashes, or leading or trailing
   whitespace must be double-quoted.  Empty strings and strings matching the
   word <literal>NULL</literal> must be quoted, too.  To put a double
   quote or backslash in a quoted array element value, precede it
   with a backslash. Alternatively, you can avoid quotes and use
   backslash-escaping to protect all data characters that would otherwise
   be taken as array syntax.
  </para>

  <para>
   You can add whitespace before a left brace or after a right
   brace. You can also add whitespace before or after any individual item
   string. In all of these cases the whitespace will be ignored. However,
   whitespace within double-quoted elements, or surrounded on both sides by
   non-whitespace characters of an element, is not ignored.
  </para>

 <tip>
  <para>
   The <literal>ARRAY</literal> constructor syntax (see
   <xref linkend="sql-syntax-array-constructors"/>) is often easier to work
   with than the array-literal syntax when writing array values in SQL
   commands. In <literal>ARRAY</literal>, individual element values are written the
   same way they would be written when not members of an array.
  </para>
 </tip>
 </sect2>

</sect1>

Title: Array Input and Output Syntax
Summary
The array output routine in PostgreSQL handles element values with special characters, NULL values, and quoted strings, and allows for explicit specification of array subscript ranges, with rules for parsing and escaping special characters, and alternative syntax using the ARRAY constructor for easier array value specification.