doc/src/sgml/ref/values.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-VALUES">
   7  <refmeta>
   8   <refentrytitle id="SQL-VALUES-TITLE">VALUES</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>VALUES</refname>
  15   <refpurpose>compute a set of rows</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-values">
  19   <primary>VALUES</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ...]
  25     [ ORDER BY <replaceable class="parameter">sort_expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ]
  26     [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
  27     [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ]
  28     [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
  29 </synopsis>
  30  </refsynopsisdiv>
  31
  32  <refsect1>
  33   <title>Description</title>
  34
  35   <para>
  36    <command>VALUES</command> computes a row value or set of row values
  37    specified by value expressions.  It is most commonly used to generate
  38    a <quote>constant table</> within a larger command, but it can be
  39    used on its own.
  40   </para>
  41
  42   <para>
  43    When more than one row is specified, all the rows must have the same
  44    number of elements.  The data types of the resulting table's columns are
  45    determined by combining the explicit or inferred types of the expressions
  46    appearing in that column, using the same rules as for <literal>UNION</>
  47    (see <xref linkend="typeconv-union-case">).
  48   </para>
  49
  50   <para>
  51    Within larger commands, <command>VALUES</> is syntactically allowed
  52    anywhere that <command>SELECT</> is.  Because it is treated like a
  53    <command>SELECT</> by the grammar, it is possible to use
  54    the <literal>ORDER BY</>, <literal>LIMIT</> (or
  55    equivalently <literal>FETCH FIRST</literal>),
  56    and <literal>OFFSET</> clauses with a
  57    <command>VALUES</> command.
  58   </para>
  59  </refsect1>
  60
  61  <refsect1>
  62   <title>Parameters</title>
  63
  64   <variablelist>
  65    <varlistentry>
  66     <term><replaceable class="PARAMETER">expression</replaceable></term>
  67     <listitem>
  68      <para>
  69       A constant or expression to compute and insert at the indicated place
  70       in the resulting table (set of rows).  In a <command>VALUES</> list
  71       appearing at the top level of an <command>INSERT</>, an
  72       <replaceable class="PARAMETER">expression</replaceable> can be replaced
  73       by <literal>DEFAULT</literal> to indicate that the destination column's
  74       default value should be inserted.  <literal>DEFAULT</literal> cannot
  75       be used when <command>VALUES</> appears in other contexts.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79
  80    <varlistentry>
  81     <term><replaceable class="parameter">sort_expression</replaceable></term>
  82     <listitem>
  83      <para>
  84       An expression or integer constant indicating how to sort the result
  85       rows.  This expression can refer to the columns of the
  86       <command>VALUES</> result as <literal>column1</>, <literal>column2</>,
  87       etc.  For more details see
  88       <xref linkend="sql-orderby" endterm="sql-orderby-title">.
  89      </para>
  90     </listitem>
  91    </varlistentry>
  92
  93    <varlistentry>
  94     <term><replaceable class="parameter">operator</replaceable></term>
  95     <listitem>
  96      <para>
  97       A sorting operator.  For details see
  98       <xref linkend="sql-orderby" endterm="sql-orderby-title">.
  99      </para>
 100     </listitem>
 101    </varlistentry>
 102
 103    <varlistentry>
 104     <term><replaceable class="parameter">count</replaceable></term>
 105     <listitem>
 106      <para>
 107       The maximum number of rows to return.  For details see
 108       <xref linkend="sql-limit" endterm="sql-limit-title">.
 109      </para>
 110     </listitem>
 111    </varlistentry>
 112
 113    <varlistentry>
 114     <term><replaceable class="parameter">start</replaceable></term>
 115     <listitem>
 116      <para>
 117       The number of rows to skip before starting to return rows.
 118       For details see
 119       <xref linkend="sql-limit" endterm="sql-limit-title">.
 120      </para>
 121     </listitem>
 122    </varlistentry>
 123   </variablelist>
 124  </refsect1>
 125
 126  <refsect1>
 127   <title>Notes</title>
 128
 129   <para>
 130    <command>VALUES</> lists with very large numbers of rows should be avoided,
 131    as you might encounter out-of-memory failures or poor performance.
 132    <command>VALUES</> appearing within <command>INSERT</> is a special case
 133    (because the desired column types are known from the <command>INSERT</>'s
 134    target table, and need not be inferred by scanning the <command>VALUES</>
 135    list), so it can handle larger lists than are practical in other contexts.
 136   </para>
 137  </refsect1>
 138
 139  <refsect1>
 140   <title>Examples</title>
 141
 142   <para>
 143    A bare <command>VALUES</> command:
 144
 145 <programlisting>
 146 VALUES (1, 'one'), (2, 'two'), (3, 'three');
 147 </programlisting>
 148
 149    This will return a table of two columns and three rows.  It's effectively
 150    equivalent to:
 151
 152 <programlisting>
 153 SELECT 1 AS column1, 'one' AS column2
 154 UNION ALL
 155 SELECT 2, 'two'
 156 UNION ALL
 157 SELECT 3, 'three';
 158 </programlisting>
 159
 160   </para>
 161
 162   <para>
 163    More usually, <command>VALUES</> is used within a larger SQL command.
 164    The most common use is in <command>INSERT</>:
 165
 166 <programlisting>
 167 INSERT INTO films (code, title, did, date_prod, kind)
 168     VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
 169 </programlisting>
 170   </para>
 171
 172   <para>
 173    In the context of <command>INSERT</>, entries of a <command>VALUES</> list
 174    can be <literal>DEFAULT</literal> to indicate that the column default
 175    should be used here instead of specifying a value:
 176
 177 <programlisting>
 178 INSERT INTO films VALUES
 179     ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),
 180     ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);
 181 </programlisting>
 182   </para>
 183
 184   <para>
 185    <command>VALUES</> can also be used where a sub-<command>SELECT</> might
 186    be written, for example in a <literal>FROM</> clause:
 187
 188 <programlisting>
 189 SELECT f.*
 190   FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)
 191   WHERE f.studio = t.studio AND f.kind = t.kind;
 192
 193 UPDATE employees SET salary = salary * v.increase
 194   FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)
 195   WHERE employees.depno = v.depno AND employees.sales &gt;= v.target;
 196 </programlisting>
 197
 198    Note that an <literal>AS</> clause is required when <command>VALUES</>
 199    is used in a <literal>FROM</> clause, just as is true for
 200    <command>SELECT</>.  It is not required that the <literal>AS</> clause
 201    specify names for all the columns, but it's good practice to do so.
 202    (The default column names for <command>VALUES</> are <literal>column1</>,
 203    <literal>column2</>, etc in <productname>PostgreSQL</productname>, but
 204    these names might be different in other database systems.)
 205   </para>
 206
 207   <para>
 208    When <command>VALUES</> is used in <command>INSERT</>, the values are all
 209    automatically coerced to the data type of the corresponding destination
 210    column.  When it's used in other contexts, it might be necessary to specify
 211    the correct data type.  If the entries are all quoted literal constants,
 212    coercing the first is sufficient to determine the assumed type for all:
 213
 214 <programlisting>
 215 SELECT * FROM machines
 216 WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));
 217 </programlisting>
 218   </para>
 219
 220   <tip>
 221    <para>
 222     For simple <literal>IN</> tests, it's better to rely on the
 223     list-of-scalars form of <literal>IN</> than to write a <command>VALUES</>
 224     query as shown above.  The list of scalars method requires less writing
 225     and is often more efficient.
 226    </para>
 227   </tip>
 228  </refsect1>
 229
 230  <refsect1>
 231   <title>Compatibility</title>
 232
 233   <para>
 234    <command>VALUES</command> conforms to the SQL standard.
 235    <literal>LIMIT</literal> and <literal>OFFSET</literal> are
 236    <productname>PostgreSQL</productname> extensions; see also
 237    under <xref linkend="sql-select" endterm="sql-select-title">.
 238   </para>
 239  </refsect1>
 240
 241  <refsect1>
 242   <title>See Also</title>
 243
 244   <simplelist type="inline">
 245    <member><xref linkend="sql-insert" endterm="sql-insert-title"></member>
 246    <member><xref linkend="sql-select" endterm="sql-select-title"></member>
 247   </simplelist>
 248  </refsect1>
 249 </refentry>