id="queries-limit">
  <title><literal>LIMIT</literal> and <literal>OFFSET</literal></title>

  <indexterm zone="queries-limit">
   <primary>LIMIT</primary>
  </indexterm>

  <indexterm zone="queries-limit">
   <primary>OFFSET</primary>
  </indexterm>

  <para>
   <literal>LIMIT</literal> and <literal>OFFSET</literal> allow you to retrieve just
   a portion of the rows that are generated by the rest of the query:
<synopsis>
SELECT <replaceable>select_list</replaceable>
    FROM <replaceable>table_expression</replaceable>
    <optional> ORDER BY ... </optional>
    <optional> LIMIT { <replaceable class="parameter">count</replaceable> | ALL } </optional>
    <optional> OFFSET <replaceable class="parameter">start</replaceable> </optional>
</synopsis>
  </para>

  <para>
   If a limit count is given, no more than that many rows will be
   returned (but possibly fewer, if the query itself yields fewer rows).
   <literal>LIMIT ALL</literal> is the same as omitting the <literal>LIMIT</literal>
   clause, as is <literal>LIMIT</literal> with a NULL argument.
  </para>

  <para>
   <literal>OFFSET</literal> says to skip that many rows before beginning to
   return rows.  <literal>OFFSET 0</literal> is the same as omitting the
   <literal>OFFSET</literal> clause, as is <literal>OFFSET</literal> with a NULL argument.
  </para>

  <para>
   If both <literal>OFFSET</literal>
   and <literal>LIMIT</literal> appear, then <literal>OFFSET</literal> rows are
   skipped before starting to count the <literal>LIMIT</literal> rows that
   are returned.
  </para>

  <para>
   When using <literal>LIMIT</literal>, it is important to use an
   <literal>ORDER BY</literal> clause that constrains the result rows into a
   unique order.  Otherwise you will get an unpredictable subset of
   the query's rows. You might be asking for the tenth through
   twentieth rows, but tenth through twentieth in what ordering? The
   ordering is unknown, unless you specified <literal>ORDER BY</literal>.
  </para>

  <para>
   The query optimizer takes <literal>LIMIT</literal> into account when
   generating query plans, so you are very likely to get different
   plans (yielding different row orders) depending on what you give
   for <literal>LIMIT</literal> and <literal>OFFSET</literal>.  Thus, using
   different <literal>LIMIT</literal>/<literal>OFFSET</literal> values to select
   different subsets of a query result <emphasis>will give
   inconsistent results</emphasis> unless you enforce a predictable
   result ordering with <literal>ORDER BY</literal>.  This is not a bug; it
   is an inherent consequence of the fact that SQL does not promise to
   deliver the results of a query in any particular order unless
   <literal>ORDER BY</literal> is used to constrain the order.
  </para>

  <para>
   The rows skipped by an <literal>OFFSET</literal> clause still have to be
   computed inside the server; therefore a large <literal>OFFSET</literal>
   might be inefficient.
  </para>
 </sect1>


 <sect1 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>