Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb5b1eb8

Browse files
committed
Documentation for VALUES lists. Joe Conway and Tom Lane
1 parent5f04ce3 commitb5b1eb8

14 files changed

+414
-61
lines changed

‎doc/src/sgml/dml.sgml

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.13 2006/02/1823:14:45 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/dml.sgml,v 1.14 2006/09/1819:54:01 tgl Exp $ -->
22

33
<chapter id="dml">
44
<title>Data Manipulation</title>
@@ -93,6 +93,16 @@ INSERT INTO products DEFAULT VALUES;
9393
</programlisting>
9494
</para>
9595

96+
<para>
97+
You can insert multiple rows in a single command:
98+
<programlisting>
99+
INSERT INTO products (product_no, name, price) VALUES
100+
(1, 'Cheese', 9.99),
101+
(2, 'Bread', 1.99),
102+
(3, 'Milk', 2.99);
103+
</programlisting>
104+
</para>
105+
96106
<tip>
97107
<para>
98108
When inserting a lot of data at the same time, considering using

‎doc/src/sgml/queries.sgml

Lines changed: 102 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.35 2006/02/1823:14:45 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/queries.sgml,v 1.36 2006/09/1819:54:01 tgl Exp $ -->
22

33
<chapter id="queries">
44
<title>Queries</title>
@@ -104,7 +104,7 @@ SELECT random();
104104
produce a virtual table that provides the rows that are passed to
105105
the select list to compute the output rows of the query.
106106
</para>
107-
107+
108108
<sect2 id="queries-from">
109109
<title>The <literal>FROM</literal> Clause</title>
110110

@@ -253,12 +253,12 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r
253253

254254
<para>
255255
<indexterm>
256-
<primary>join</primary>
257-
<secondary>natural</secondary>
258-
</indexterm>
256+
<primary>join</primary>
257+
<secondary>natural</secondary>
258+
</indexterm>
259259
<indexterm>
260-
<primary>natural join</primary>
261-
</indexterm>
260+
<primary>natural join</primary>
261+
</indexterm>
262262
Finally, <literal>NATURAL</> is a shorthand form of
263263
<literal>USING</>: it forms a <literal>USING</> list
264264
consisting of exactly those column names that appear in both
@@ -511,33 +511,36 @@ SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.i
511511
<programlisting>
512512
SELECT * FROM my_table AS m WHERE my_table.a &gt; 5;
513513
</programlisting>
514-
is not valid SQL syntax. What will actually happen (this is a
515-
<productname>PostgreSQL</productname> extension to the standard)
516-
is that an implicit table reference is added to the
514+
is not valid according to the SQL standard. In
515+
<productname>PostgreSQL</productname> this will draw an error if the
516+
<xref linkend="guc-add-missing-from"> configuration variable is
517+
<literal>off</>. If it is <literal>on</>, an implicit table reference
518+
will be added to the
517519
<literal>FROM</literal> clause, so the query is processed as if
518520
it were written as
519521
<programlisting>
520522
SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a &gt; 5;
521523
</programlisting>
522-
which will result in a cross join, which is usually not what you
523-
want.
524+
That will result in a cross join, which is usually not what you want.
524525
</para>
525526

526527
<para>
527528
Table aliases are mainly for notational convenience, but it is
528529
necessary to use them when joining a table to itself, e.g.,
529530
<programlisting>
530-
SELECT * FROMmy_table ASa CROSSJOINmy_table ASb ...
531+
SELECT * FROMpeople ASmotherJOINpeople ASchild ON mother.id = child.mother_id;
531532
</programlisting>
532533
Additionally, an alias is required if the table reference is a
533534
subquery (see <xref linkend="queries-subqueries">).
534535
</para>
535536

536537
<para>
537-
Parentheses are used to resolve ambiguities. The following
538-
statement will assign the alias <literal>b</literal> to the
539-
result of the join, unlike the previous example:
538+
Parentheses are used to resolve ambiguities. In the following example,
539+
the first statement assigns the alias <literal>b</literal> to the second
540+
instance of <literal>my_table</>, but the second statement assigns the
541+
alias to the result of the join:
540542
<programlisting>
543+
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
541544
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
542545
</programlisting>
543546
</para>
@@ -592,6 +595,17 @@ FROM (SELECT * FROM table1) AS alias_name
592595
reduced to a plain join, arise when the subquery involves
593596
grouping or aggregation.
594597
</para>
598+
599+
<para>
600+
A subquery can also be a <command>VALUES</> list:
601+
<programlisting>
602+
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
603+
AS names(first, last)
604+
</programlisting>
605+
Again, a table alias is required. Assigning alias names to the columns
606+
of the <command>VALUES</> list is optional, but is good practice.
607+
For more information see <xref linkend="queries-values">.
608+
</para>
595609
</sect3>
596610

597611
<sect3 id="queries-tablefunctions">
@@ -814,7 +828,7 @@ SELECT <replaceable>select_list</replaceable>
814828
(3 rows)
815829
</screen>
816830
</para>
817-
831+
818832
<para>
819833
In the second query, we could not have written <literal>SELECT *
820834
FROM test1 GROUP BY x</literal>, because there is no single value
@@ -1194,7 +1208,7 @@ SELECT DISTINCT ON (<replaceable>expression</replaceable> <optional>, <replaceab
11941208
<indexterm zone="queries-order">
11951209
<primary>ORDER BY</primary>
11961210
</indexterm>
1197-
1211+
11981212
<para>
11991213
After a query has produced an output table (after the select list
12001214
has been processed) it can optionally be sorted. If sorting is not
@@ -1335,4 +1349,74 @@ SELECT <replaceable>select_list</replaceable>
13351349
</para>
13361350
</sect1>
13371351

1352+
1353+
<sect1 id="queries-values">
1354+
<title><literal>VALUES</literal> Lists</title>
1355+
1356+
<indexterm zone="queries-values">
1357+
<primary>VALUES</primary>
1358+
</indexterm>
1359+
1360+
<para>
1361+
<literal>VALUES</> provides a way to generate a <quote>constant table</>
1362+
that can be used in a query without having to actually create and populate
1363+
a table on-disk. The syntax is
1364+
<synopsis>
1365+
VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) [, ...]
1366+
</synopsis>
1367+
Each parenthesized list of expressions generates a row in the table.
1368+
The lists must all have the same number of elements (i.e., the number
1369+
of columns in the table), and corresponding entries in each list must
1370+
have compatible datatypes. The actual datatype assigned to each column
1371+
of the result is determined using the same rules as for <literal>UNION</>
1372+
(see <xref linkend="typeconv-union-case">).
1373+
</para>
1374+
1375+
<para>
1376+
As an example,
1377+
1378+
<programlisting>
1379+
VALUES (1, 'one'), (2, 'two'), (3, 'three');
1380+
</programlisting>
1381+
1382+
will return a table of two columns and three rows. It's effectively
1383+
equivalent to
1384+
1385+
<programlisting>
1386+
SELECT 1 AS column1, 'one' AS column2
1387+
UNION ALL
1388+
SELECT 2, 'two'
1389+
UNION ALL
1390+
SELECT 3, 'three';
1391+
</programlisting>
1392+
1393+
By default, <productname>PostgreSQL</productname> assigns the names
1394+
<literal>column1</>, <literal>column2</>, etc. to the columns of a
1395+
<literal>VALUES</> table. The column names are not specified by the
1396+
SQL standard and different database systems do it differently, so
1397+
it's usually better to override the default names with a table alias
1398+
list.
1399+
</para>
1400+
1401+
<para>
1402+
Syntactically, <literal>VALUES</> followed by expression lists is
1403+
treated as equivalent to
1404+
<synopsis>
1405+
SELECT <replaceable>select_list</replaceable> FROM <replaceable>table_expression</replaceable>
1406+
</synopsis>
1407+
and can appear anywhere a <literal>SELECT</> can. For example, you can
1408+
use it as an arm of a <literal>UNION</>, or attach a
1409+
<replaceable>sort_specification</replaceable> (<literal>ORDER BY</>,
1410+
<literal>LIMIT</>, and/or <literal>OFFSET</>) to it. <literal>VALUES</>
1411+
is most commonly used as the data source in an <command>INSERT</> command,
1412+
and next most commonly as a subquery.
1413+
</para>
1414+
1415+
<para>
1416+
For more information see <xref linkend="sql-values"
1417+
endterm="sql-values-title">.
1418+
</para>
1419+
1420+
</sect1>
1421+
13381422
</chapter>

‎doc/src/sgml/ref/allfiles.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.67 2005/11/21 12:49:30 alvherre Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.68 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
Complete list of usable sgml source files in this directory.
55
-->
@@ -116,6 +116,7 @@ Complete list of usable sgml source files in this directory.
116116
<!entity unlisten system "unlisten.sgml">
117117
<!entity update system "update.sgml">
118118
<!entity vacuum system "vacuum.sgml">
119+
<!entity values system "values.sgml">
119120

120121
<!-- applications and utilities -->
121122
<!entity clusterdb system "clusterdb.sgml">

‎doc/src/sgml/ref/copy.sgml

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.76 2006/09/16 00:30:17 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.77 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -107,7 +107,9 @@ COPY { <replaceable class="parameter">tablename</replaceable> [ ( <replaceable c
107107
<term><replaceable class="parameter">query</replaceable></term>
108108
<listitem>
109109
<para>
110-
A <command>SELECT</> query whose results are to be copied.
110+
A <xref linkend="sql-select" endterm="sql-select-title"> or
111+
<xref linkend="sql-values" endterm="sql-values-title"> command
112+
whose results are to be copied.
111113
Note that parentheses are required around the query.
112114
</para>
113115
</listitem>

‎doc/src/sgml/ref/create_table_as.sgml

Lines changed: 8 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.35 2006/09/16 00:30:17 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.36 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -34,9 +34,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
3434

3535
<para>
3636
<command>CREATE TABLE AS</command> creates a table and fills it
37-
with data computed by a <command>SELECT</command> command or an
38-
<command>EXECUTE</command> that runs a prepared
39-
<command>SELECT</command> command. The table columns have the
37+
with data computed by a <command>SELECT</command> command.
38+
The table columns have the
4039
names and data types associated with the output columns of the
4140
<command>SELECT</command> (except that you can override the column
4241
names by giving an explicit list of new column names).
@@ -196,12 +195,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name
196195
<term><replaceable>query</replaceable></term>
197196
<listitem>
198197
<para>
199-
A query statement (that is, a <command>SELECT</command> command
200-
or an <command>EXECUTE</command> command that runs a prepared
201-
<command>SELECT</command> command). Refer to <xref
202-
linkend="sql-select" endterm="sql-select-title"> or <xref
203-
linkend="sql-execute" endterm="sql-execute-title">,
204-
respectively, for a description of the allowed syntax.
198+
A <xref linkend="sql-select" endterm="sql-select-title"> or
199+
<xref linkend="sql-values" endterm="sql-values-title"> command,
200+
or an <xref linkend="sql-execute" endterm="sql-execute-title"> command
201+
that runs a prepared <command>SELECT</> or <command>VALUES</> query.
205202
</para>
206203
</listitem>
207204
</varlistentry>
@@ -326,6 +323,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
326323
<member><xref linkend="sql-execute" endterm="sql-execute-title"></member>
327324
<member><xref linkend="sql-select" endterm="sql-select-title"></member>
328325
<member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member>
326+
<member><xref linkend="sql-values" endterm="sql-values-title"></member>
329327
</simplelist>
330328
</refsect1>
331329

‎doc/src/sgml/ref/create_view.sgml

Lines changed: 4 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.32 2006/09/16 00:30:17 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_view.sgml,v 1.33 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -99,13 +99,9 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW <replaceable class="PARAMETER">n
9999
<term><replaceable class="parameter">query</replaceable></term>
100100
<listitem>
101101
<para>
102-
A query (that is, a <command>SELECT</> statement) which will
103-
provide the columns and rows of the view.
104-
</para>
105-
106-
<para>
107-
Refer to <xref linkend="sql-select" endterm="sql-select-title">
108-
for more information about valid queries.
102+
A <xref linkend="sql-select" endterm="sql-select-title"> or
103+
<xref linkend="sql-values" endterm="sql-values-title"> command
104+
which will provide the columns and rows of the view.
109105
</para>
110106
</listitem>
111107
</varlistentry>

‎doc/src/sgml/ref/declare.sgml

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.38 2006/09/16 00:30:18 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.39 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -157,10 +157,9 @@ DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITI
157157
<term><replaceable class="parameter">query</replaceable></term>
158158
<listitem>
159159
<para>
160-
A <command>SELECT</> command that will provide the rows to be
161-
returned by the cursor. Refer to <xref linkend="sql-select"
162-
endterm="sql-select-title"> for further information about valid
163-
queries.
160+
A <xref linkend="sql-select" endterm="sql-select-title"> or
161+
<xref linkend="sql-values" endterm="sql-values-title"> command
162+
which will provide the rows to be returned by the cursor.
164163
</para>
165164
</listitem>
166165
</varlistentry>

‎doc/src/sgml/ref/explain.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/explain.sgml,v 1.37 2006/09/16 00:30:18 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/explain.sgml,v 1.38 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -121,8 +121,8 @@ ROLLBACK;
121121
<listitem>
122122
<para>
123123
Any <command>SELECT</>, <command>INSERT</>, <command>UPDATE</>,
124-
<command>DELETE</>, <command>EXECUTE</>,or<command>DECLARE</>
125-
statement, whose execution plan you wish to see.
124+
<command>DELETE</>, <command>VALUES</>, <command>EXECUTE</>, or
125+
<command>DECLARE</>statement, whose execution plan you wish to see.
126126
</para>
127127
</listitem>
128128
</varlistentry>

‎doc/src/sgml/ref/insert.sgml

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.32 2006/09/16 00:30:18 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.33 2006/09/18 19:54:01 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -31,8 +31,8 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
3131

3232
<para>
3333
<command>INSERT</command> inserts new rows into a table.
34-
One can insert rows specified by value expressions,
35-
orrows computed as a result of a query.
34+
One can insertone or morerows specified by value expressions,
35+
orzero or more rows resulting from a query.
3636
</para>
3737

3838
<para>
@@ -67,8 +67,9 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
6767
</para>
6868

6969
<para>
70-
You must have <literal>INSERT</literal> privilege to a table in
71-
order to insert into it. If you use the <replaceable
70+
You must have <literal>INSERT</literal> privilege on a table in
71+
order to insert into it, and <literal>SELECT</> privilege on it to
72+
use <literal>RETURNING</>. If you use the <replaceable
7273
class="PARAMETER">query</replaceable> clause to insert rows from a
7374
query, you also need to have <literal>SELECT</literal> privilege on
7475
any table used in the query.
@@ -232,6 +233,16 @@ INSERT INTO films DEFAULT VALUES;
232233
</programlisting>
233234
</para>
234235

236+
<para>
237+
To insert multiple rows using the multi-row <command>VALUES</> syntax:
238+
239+
<programlisting>
240+
INSERT INTO films (code, title, did, date_prod, kind) VALUES
241+
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
242+
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
243+
</programlisting>
244+
</para>
245+
235246
<para>
236247
This example inserts some rows into table
237248
<literal>films</literal> from a table <literal>tmp_films</literal>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp