diff options
Diffstat (limited to 'doc/src/sgml/ref/values.sgml')
| -rw-r--r-- | doc/src/sgml/ref/values.sgml | 251 |
1 files changed, 251 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/values.sgml b/doc/src/sgml/ref/values.sgml new file mode 100644 index 0000000..4bf7bfd --- /dev/null +++ b/doc/src/sgml/ref/values.sgml @@ -0,0 +1,251 @@ +<!-- +doc/src/sgml/ref/values.sgml +PostgreSQL documentation +--> + +<refentry id="sql-values"> + <indexterm zone="sql-values"> + <primary>VALUES</primary> + </indexterm> + + <refmeta> + <refentrytitle>VALUES</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>VALUES</refname> + <refpurpose>compute a set of rows</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +VALUES ( <replaceable class="parameter">expression</replaceable> [, ...] ) [, ...] + [ ORDER BY <replaceable class="parameter">sort_expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [, ...] ] + [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ] + [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ] + [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>VALUES</command> computes a row value or set of row values + specified by value expressions. It is most commonly used to generate + a <quote>constant table</quote> within a larger command, but it can be + used on its own. + </para> + + <para> + When more than one row is specified, all the rows must have the same + number of elements. The data types of the resulting table's columns are + determined by combining the explicit or inferred types of the expressions + appearing in that column, using the same rules as for <literal>UNION</literal> + (see <xref linkend="typeconv-union-case"/>). + </para> + + <para> + Within larger commands, <command>VALUES</command> is syntactically allowed + anywhere that <command>SELECT</command> is. Because it is treated like a + <command>SELECT</command> by the grammar, it is possible to use + the <literal>ORDER BY</literal>, <literal>LIMIT</literal> (or + equivalently <literal>FETCH FIRST</literal>), + and <literal>OFFSET</literal> clauses with a + <command>VALUES</command> command. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">expression</replaceable></term> + <listitem> + <para> + A constant or expression to compute and insert at the indicated place + in the resulting table (set of rows). In a <command>VALUES</command> list + appearing at the top level of an <command>INSERT</command>, an + <replaceable class="parameter">expression</replaceable> can be replaced + by <literal>DEFAULT</literal> to indicate that the destination column's + default value should be inserted. <literal>DEFAULT</literal> cannot + be used when <command>VALUES</command> appears in other contexts. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">sort_expression</replaceable></term> + <listitem> + <para> + An expression or integer constant indicating how to sort the result + rows. This expression can refer to the columns of the + <command>VALUES</command> result as <literal>column1</literal>, <literal>column2</literal>, + etc. For more details see + <xref linkend="sql-orderby"/> + in the <xref linkend="sql-select"/> documentation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">operator</replaceable></term> + <listitem> + <para> + A sorting operator. For details see + <xref linkend="sql-orderby"/> + in the <xref linkend="sql-select"/> documentation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">count</replaceable></term> + <listitem> + <para> + The maximum number of rows to return. For details see + <xref linkend="sql-limit"/> + in the <xref linkend="sql-select"/> documentation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">start</replaceable></term> + <listitem> + <para> + The number of rows to skip before starting to return rows. + For details see <xref linkend="sql-limit"/> + in the <xref linkend="sql-select"/> documentation. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + <command>VALUES</command> lists with very large numbers of rows should be avoided, + as you might encounter out-of-memory failures or poor performance. + <command>VALUES</command> appearing within <command>INSERT</command> is a special case + (because the desired column types are known from the <command>INSERT</command>'s + target table, and need not be inferred by scanning the <command>VALUES</command> + list), so it can handle larger lists than are practical in other contexts. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + A bare <command>VALUES</command> command: + +<programlisting> +VALUES (1, 'one'), (2, 'two'), (3, 'three'); +</programlisting> + + This 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> + + </para> + + <para> + More usually, <command>VALUES</command> is used within a larger SQL command. + The most common use is in <command>INSERT</command>: + +<programlisting> +INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); +</programlisting> + </para> + + <para> + In the context of <command>INSERT</command>, entries of a <command>VALUES</command> list + can be <literal>DEFAULT</literal> to indicate that the column default + should be used here instead of specifying a value: + +<programlisting> +INSERT INTO films VALUES + ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'), + ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT); +</programlisting> + </para> + + <para> + <command>VALUES</command> can also be used where a sub-<command>SELECT</command> might + be written, for example in a <literal>FROM</literal> clause: + +<programlisting> +SELECT f.* + FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind) + WHERE f.studio = t.studio AND f.kind = t.kind; + +UPDATE employees SET salary = salary * v.increase + FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase) + WHERE employees.depno = v.depno AND employees.sales >= v.target; +</programlisting> + + Note that an <literal>AS</literal> clause is required when <command>VALUES</command> + is used in a <literal>FROM</literal> clause, just as is true for + <command>SELECT</command>. It is not required that the <literal>AS</literal> clause + specify names for all the columns, but it's good practice to do so. + (The default column names for <command>VALUES</command> are <literal>column1</literal>, + <literal>column2</literal>, etc. in <productname>PostgreSQL</productname>, but + these names might be different in other database systems.) + </para> + + <para> + When <command>VALUES</command> is used in <command>INSERT</command>, the values are all + automatically coerced to the data type of the corresponding destination + column. When it's used in other contexts, it might be necessary to specify + the correct data type. If the entries are all quoted literal constants, + coercing the first is sufficient to determine the assumed type for all: + +<programlisting> +SELECT * FROM machines +WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43')); +</programlisting></para> + + <tip> + <para> + For simple <literal>IN</literal> tests, it's better to rely on the + <link linkend="functions-comparisons-in-scalar">list-of-scalars</link> + form of <literal>IN</literal> than to write a <command>VALUES</command> + query as shown above. The list of scalars method requires less writing + and is often more efficient. + </para> + </tip> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para><command>VALUES</command> conforms to the SQL standard. + <literal>LIMIT</literal> and <literal>OFFSET</literal> are + <productname>PostgreSQL</productname> extensions; see also + under <xref linkend="sql-select"/>. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-insert"/></member> + <member><xref linkend="sql-select"/></member> + </simplelist> + </refsect1> +</refentry> |