id="queries-values">
  <title><literal>VALUES</literal> Lists</title>

  <indexterm zone="queries-values">
   <primary>VALUES</primary>
  </indexterm>

  <para>
   <literal>VALUES</literal> provides a way to generate a <quote>constant table</quote>
   that can be used in a query without having to actually create and populate
   a table on-disk.  The syntax is
<synopsis>
VALUES ( <replaceable class="parameter">expression</replaceable> [, ...] ) [, ...]
</synopsis>
   Each parenthesized list of expressions generates a row in the table.
   The lists must all have the same number of elements (i.e., the number
   of columns in the table), and corresponding entries in each list must
   have compatible data types.  The actual data type assigned to each column
   of the result is determined using the same rules as for <literal>UNION</literal>
   (see <xref linkend="typeconv-union-case"/>).
  </para>

  <para>
   As an example:
<programlisting>
VALUES (1, 'one'), (2, 'two'), (3, 'three');
</programlisting>

   will return a table of two columns and three rows.  It's effectively
   equivalent to:
<programlisting>
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
</programlisting>

   By default, <productname>PostgreSQL</productname> assigns the names
   <literal>column1</literal>, <literal>column2</literal>, etc. to the columns of a
   <literal>VALUES</literal> table.  The column names are not specified by the
   SQL standard and different database systems do it differently, so
   it's usually better to override the default names with a table alias
   list, like this:
<programlisting>
=&gt; SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)
</programlisting>
  </para>

  <para>
   Syntactically, <literal>VALUES</literal> followed by expression lists is
   treated as equivalent to:
<synopsis>
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
</synopsis>
   and can appear anywhere a <literal>SELECT</literal> can.  For example, you can
   use it as part of a <literal>UNION</literal>, or attach a
   <replaceable>sort_specification</replaceable> (<literal>ORDER BY</literal>,
   <literal>LIMIT</literal>, and/or <literal>OFFSET</literal>) to it.  <literal>VALUES</literal>
   is most commonly used as the data source in an <command>INSERT</command> command,
   and next most commonly as a subquery.
  </para>

  <para>
   For more information see <xref linkend="sql-values"/>.
  </para>

 </sect1>


 <sect1 id="queries-with">
  <title><literal>WITH</literal> Queries (Common Table Expressions)</title>

  <indexterm zone="queries-with">
   <primary>WITH</primary>
   <secondary>in SELECT</secondary>
  </indexterm>

  <indexterm>
   <primary>common table expression</primary>
   <see>WITH</see>
  </indexterm>

  <para>
   <literal>WITH</literal> provides a way to write auxiliary statements for use in a
   larger query.  These statements, which are often referred to as Common
   Table Expressions or <acronym>CTE</acronym>s, can be thought of as defining
   temporary tables that exist just for one query.  Each auxiliary statement
   in a <literal>WITH</literal> clause can be a <command>SELECT</command>,
   <command>INSERT</command>, <command>UPDATE</command>, <command>DELETE</command>,
   or <command>MERGE</command>; and the
   <literal>WITH</literal> clause itself is attached to a primary statement that can
   also be a <command>SELECT</command>, <command>INSERT</command>, <command>UPDATE</command>,
   <command>DELETE</command>, or <command>MERGE</command>.
  </para>

 <sect2 id="queries-with-select">
   <title><command>SELECT</command> in <literal>WITH</literal></title>

  <para>
   The basic value of <command>SELECT</command> in <literal>WITH</literal> is to
   break down complicated queries into simpler parts.  An example is:

<programlisting>
WITH regional_sales AS