Debian Codegraph @

doc/src/sgml/ref/values.sgml

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