SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";
</programlisting>
    Note that in the latter case the <literal>COLLATE</literal> clause is
    attached to an input argument of the operator we wish to affect.
    It doesn't matter which argument of the operator or function call the
    <literal>COLLATE</literal> clause is attached to, because the collation that is
    applied by the operator or function is derived by considering all
    arguments, and an explicit <literal>COLLATE</literal> clause will override the
    collations of all other arguments.  (Attaching non-matching
    <literal>COLLATE</literal> clauses to more than one argument, however, is an
    error.  For more details see <xref linkend="collation"/>.)
    Thus, this gives the same result as the previous example:
<programlisting>
SELECT * FROM tbl WHERE a COLLATE "C" &gt; 'foo';
</programlisting>
    But this is an error:
<programlisting>
SELECT * FROM tbl WHERE (a &gt; 'foo') COLLATE "C";
</programlisting>
    because it attempts to apply a collation to the result of the
    <literal>&gt;</literal> operator, which is of the non-collatable data type
    <type>boolean</type>.
   </para>
  </sect2>

  <sect2 id="sql-syntax-scalar-subqueries">
   <title>Scalar Subqueries</title>

   <indexterm>
    <primary>subquery</primary>
   </indexterm>

   <para>
    A scalar subquery is an ordinary
    <command>SELECT</command> query in parentheses that returns exactly one
    row with one column.  (See <xref linkend="queries"/> for information about writing queries.)
    The <command>SELECT</command> query is executed
    and the single returned value is used in the surrounding value expression.
    It is an error to use a query that
    returns more than one row or more than one column as a scalar subquery.
    (But if, during a particular execution, the subquery returns no rows,
    there is no error; the scalar result is taken to be null.)
    The subquery can refer to variables from the surrounding query,
    which will act as constants during any one evaluation of the subquery.
    See also <xref linkend="functions-subquery"/> for other expressions involving subqueries.
   </para>

   <para>
    For example, the following finds the largest city population in each
    state:
<programlisting>
SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
    FROM states;
</programlisting>
   </para>
  </sect2>

  <sect2 id="sql-syntax-array-constructors">
   <title>Array Constructors</title>

   <indexterm>
    <primary>array</primary>
    <secondary>constructor</secondary>
   </indexterm>

   <indexterm>
    <primary>ARRAY</primary>
   </indexterm>

   <para>
    An array constructor is an expression that builds an
    array value using values for its member elements.  A simple array
    constructor
    consists of the key word <literal>ARRAY</literal>, a left square bracket
    <literal>[</literal>, a list of expressions (separated by commas) for the
    array element values, and finally a right square bracket <literal>]</literal>.
    For example:
<programlisting>
SELECT ARRAY[1,2,3+4];
  array
---------
 {1,2,7}
(1 row)
</programlisting>
    By default,
    the array element type is the common type of the member expressions,
    determined using the same rules as for <literal>UNION</literal> or
    <literal>CASE</literal> constructs (see <xref linkend="typeconv-union-case"/>).
    You can override this by explicitly casting the array constructor to the
    desired type, for example:
<programlisting>
SELECT ARRAY[1,2,22.7]::integer[];
  array
----------
 {1,2,23}
(1 row)
</programlisting>
    This has the same effect as casting each expression to the array
    element type individually.
    For more on casting, see <xref linkend="sql-syntax-type-casts"/>.
   </para>

   <para>
    Multidimensional array values can be built by nesting array
    constructors.
    In the inner constructors, the key word <literal>ARRAY</literal> can
    be omitted.  For example,