| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/values.sgml,v 1.2 2006/10/23 18:10:32 petere Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-VALUES"> |
| <refmeta> |
| <refentrytitle id="SQL-VALUES-TITLE">VALUES</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>VALUES</refname> |
| <refpurpose>compute a set of rows</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-values"> |
| <primary>VALUES</primary> |
| </indexterm> |
| |
| <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> ] |
| </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</> 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</> |
| (see <xref linkend="typeconv-union-case">). |
| </para> |
| |
| <para> |
| Within larger commands, <command>VALUES</> is syntactically allowed |
| anywhere that <command>SELECT</> is. Because it is treated like a |
| <command>SELECT</> by the grammar, it is possible to use the <literal>ORDER |
| BY</>, <literal>LIMIT</>, and <literal>OFFSET</> clauses with a |
| <command>VALUES</> 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</> list |
| appearing at the top level of an <command>INSERT</>, 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</> 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 may refer to the columns of the |
| <command>VALUES</> result as <literal>column1</>, <literal>column2</>, |
| etc. For more details see |
| <xref linkend="sql-orderby" endterm="sql-orderby-title">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">operator</replaceable></term> |
| <listitem> |
| <para> |
| A sorting operator. For details see |
| <xref linkend="sql-orderby" endterm="sql-orderby-title">. |
| </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" endterm="sql-limit-title">. |
| </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" endterm="sql-limit-title">. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| <command>VALUES</> lists with very large numbers of rows should be avoided, |
| as you may encounter out-of-memory failures or poor performance. |
| <command>VALUES</> appearing within <command>INSERT</> is a special case |
| (because the desired column types are known from the <command>INSERT</>'s |
| target table, and need not be inferred by scanning the <command>VALUES</> |
| 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: |
| |
| <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</> is used within a larger SQL command. |
| The most common use is in <command>INSERT</>: |
| |
| <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</>, entries of a <command>VALUES</> 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</> can also be used where a sub-<command>SELECT</> might |
| be written, for example in a <literal>FROM</> 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</> clause is required when <command>VALUES</> |
| is used in a <literal>FROM</> clause, just as is true for |
| <command>SELECT</>. It is not required that the <literal>AS</> clause |
| specify names for all the columns, but it's good practice to do so. |
| (The default column names for <command>VALUES</> are <literal>column1</>, |
| <literal>column2</>, etc in <productname>PostgreSQL</productname>, but |
| these names might be different in other database systems.) |
| </para> |
| |
| <para> |
| When <command>VALUES</> is used in <command>INSERT</>, the values are all |
| automatically coerced to the data type of the corresponding destination |
| column. When it's used in other contexts, it may 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</> tests, it's better to rely on the |
| list-of-scalars form of <literal>IN</> than to write a <command>VALUES</> |
| 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, except that |
| <literal>LIMIT</literal> and <literal>OFFSET</literal> are |
| <productname>PostgreSQL</productname> extensions. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-insert" endterm="sql-insert-title"></member> |
| <member><xref linkend="sql-select" endterm="sql-select-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |