From b5b1eb80b70222231946db363d87a7077ece6a64 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 18 Sep 2006 19:54:01 +0000 Subject: [PATCH] Documentation for VALUES lists. Joe Conway and Tom Lane --- doc/src/sgml/dml.sgml | 12 +- doc/src/sgml/queries.sgml | 120 ++++++++++++++--- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/copy.sgml | 6 +- doc/src/sgml/ref/create_table_as.sgml | 18 ++- doc/src/sgml/ref/create_view.sgml | 12 +- doc/src/sgml/ref/declare.sgml | 9 +- doc/src/sgml/ref/explain.sgml | 6 +- doc/src/sgml/ref/insert.sgml | 21 ++- doc/src/sgml/ref/prepare.sgml | 4 +- doc/src/sgml/ref/select.sgml | 6 +- doc/src/sgml/ref/values.sgml | 244 ++++++++++++++++++++++++++++++++++ doc/src/sgml/reference.sgml | 3 +- doc/src/sgml/typeconv.sgml | 11 +- 14 files changed, 414 insertions(+), 61 deletions(-) create mode 100644 doc/src/sgml/ref/values.sgml diff --git a/doc/src/sgml/dml.sgml b/doc/src/sgml/dml.sgml index fcea6ad65f..aed0a60a02 100644 --- a/doc/src/sgml/dml.sgml +++ b/doc/src/sgml/dml.sgml @@ -1,4 +1,4 @@ - + Data Manipulation @@ -93,6 +93,16 @@ INSERT INTO products DEFAULT VALUES; + + You can insert multiple rows in a single command: + +INSERT INTO products (product_no, name, price) VALUES + (1, 'Cheese', 9.99), + (2, 'Bread', 1.99), + (3, 'Milk', 2.99); + + + When inserting a lot of data at the same time, considering using diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index cb8f2a1e5a..8cf0268fd9 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -1,4 +1,4 @@ - + Queries @@ -104,7 +104,7 @@ SELECT random(); produce a virtual table that provides the rows that are passed to the select list to compute the output rows of the query. - + The <literal>FROM</literal> Clause @@ -253,12 +253,12 @@ FROM table_reference , table_r - join - natural - + join + natural + - natural join - + natural join + Finally, NATURAL is a shorthand form of USING: it forms a USING list 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 SELECT * FROM my_table AS m WHERE my_table.a > 5; - is not valid SQL syntax. What will actually happen (this is a - PostgreSQL extension to the standard) - is that an implicit table reference is added to the + is not valid according to the SQL standard. In + PostgreSQL this will draw an error if the + configuration variable is + off. If it is on, an implicit table reference + will be added to the FROM clause, so the query is processed as if it were written as SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5; - which will result in a cross join, which is usually not what you - want. + That will result in a cross join, which is usually not what you want. Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g., -SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... +SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id; Additionally, an alias is required if the table reference is a subquery (see ). - Parentheses are used to resolve ambiguities. The following - statement will assign the alias b to the - result of the join, unlike the previous example: + Parentheses are used to resolve ambiguities. In the following example, + the first statement assigns the alias b to the second + instance of my_table, but the second statement assigns the + alias to the result of the join: +SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ... @@ -592,6 +595,17 @@ FROM (SELECT * FROM table1) AS alias_name reduced to a plain join, arise when the subquery involves grouping or aggregation. + + + A subquery can also be a VALUES list: + +FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) + AS names(first, last) + + Again, a table alias is required. Assigning alias names to the columns + of the VALUES list is optional, but is good practice. + For more information see . + @@ -814,7 +828,7 @@ SELECT select_list (3 rows) - + In the second query, we could not have written SELECT * FROM test1 GROUP BY x, because there is no single value @@ -1194,7 +1208,7 @@ SELECT DISTINCT ON (expression , ORDER BY - + After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not @@ -1335,4 +1349,74 @@ SELECT select_list + + + <literal>VALUES</literal> Lists + + + VALUES + + + + VALUES provides a way to generate a constant table + that can be used in a query without having to actually create and populate + a table on-disk. The syntax is + +VALUES ( expression [, ...] ) [, ...] + + Each parenthesized list of expressions generates a row in the table. + The lists must all have the same number of elements (i.e., the number + of columns in the table), and corresponding entries in each list must + have compatible datatypes. The actual datatype assigned to each column + of the result is determined using the same rules as for UNION + (see ). + + + + As an example, + + +VALUES (1, 'one'), (2, 'two'), (3, 'three'); + + + will return a table of two columns and three rows. It's effectively + equivalent to + + +SELECT 1 AS column1, 'one' AS column2 +UNION ALL +SELECT 2, 'two' +UNION ALL +SELECT 3, 'three'; + + + By default, PostgreSQL assigns the names + column1, column2, etc. to the columns of a + VALUES table. The column names are not specified by the + SQL standard and different database systems do it differently, so + it's usually better to override the default names with a table alias + list. + + + + Syntactically, VALUES followed by expression lists is + treated as equivalent to + +SELECT select_list FROM table_expression + + and can appear anywhere a SELECT can. For example, you can + use it as an arm of a UNION, or attach a + sort_specification (ORDER BY, + LIMIT, and/or OFFSET) to it. VALUES + is most commonly used as the data source in an INSERT command, + and next most commonly as a subquery. + + + + For more information see . + + + + diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 07dd0b0ee7..a481500525 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -116,6 +116,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 042ec818f0..6eab13705f 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -107,7 +107,9 @@ COPY { tablename [ ( query - A SELECT query whose results are to be copied. + A or + command + whose results are to be copied. Note that parentheses are required around the query. diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 05cc38cf33..36348c8ad4 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,5 +1,5 @@ @@ -34,9 +34,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name CREATE TABLE AS creates a table and fills it - with data computed by a SELECT command or an - EXECUTE that runs a prepared - SELECT command. The table columns have the + with data computed by a SELECT command. + The table columns have the names and data types associated with the output columns of the SELECT (except that you can override the column names by giving an explicit list of new column names). @@ -196,12 +195,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name query - A query statement (that is, a SELECT command - or an EXECUTE command that runs a prepared - SELECT command). Refer to or , - respectively, for a description of the allowed syntax. + A or + command, + or an command + that runs a prepared SELECT or VALUES query. @@ -326,6 +323,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS + diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 6bccbb681f..f6e75ce3e2 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -1,5 +1,5 @@ @@ -99,13 +99,9 @@ CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW n query - A query (that is, a SELECT statement) which will - provide the columns and rows of the view. - - - - Refer to - for more information about valid queries. + A or + command + which will provide the columns and rows of the view. diff --git a/doc/src/sgml/ref/declare.sgml b/doc/src/sgml/ref/declare.sgml index 475db0d022..30f924268c 100644 --- a/doc/src/sgml/ref/declare.sgml +++ b/doc/src/sgml/ref/declare.sgml @@ -1,5 +1,5 @@ @@ -157,10 +157,9 @@ DECLARE name [ BINARY ] [ INSENSITI query - A SELECT command that will provide the rows to be - returned by the cursor. Refer to for further information about valid - queries. + A or + command + which will provide the rows to be returned by the cursor. diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 2853f6c79f..b232fb578f 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -1,5 +1,5 @@ @@ -121,8 +121,8 @@ ROLLBACK; Any SELECT, INSERT, UPDATE, - DELETE, EXECUTE, or DECLARE - statement, whose execution plan you wish to see. + DELETE, VALUES, EXECUTE, or + DECLARE statement, whose execution plan you wish to see. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 82b3260668..140ec7745f 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -1,5 +1,5 @@ @@ -31,8 +31,8 @@ INSERT INTO table [ ( INSERT inserts new rows into a table. - One can insert rows specified by value expressions, - or rows computed as a result of a query. + One can insert one or more rows specified by value expressions, + or zero or more rows resulting from a query. @@ -67,8 +67,9 @@ INSERT INTO table [ ( - You must have INSERT privilege to a table in - order to insert into it. If you use the INSERT privilege on a table in + order to insert into it, and SELECT privilege on it to + use RETURNING. If you use the query clause to insert rows from a query, you also need to have SELECT privilege on any table used in the query. @@ -233,6 +234,16 @@ INSERT INTO films DEFAULT VALUES; + To insert multiple rows using the multi-row VALUES syntax: + + +INSERT INTO films (code, title, did, date_prod, kind) VALUES + ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), + ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); + + + + This example inserts some rows into table films from a table tmp_films with the same column layout as films: diff --git a/doc/src/sgml/ref/prepare.sgml b/doc/src/sgml/ref/prepare.sgml index e916fee1d2..b4d9ab64dc 100644 --- a/doc/src/sgml/ref/prepare.sgml +++ b/doc/src/sgml/ref/prepare.sgml @@ -1,5 +1,5 @@ @@ -116,7 +116,7 @@ PREPARE name [ ( Any SELECT, INSERT, UPDATE, - or DELETE statement. + DELETE, or VALUES statement. diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index c73e0a1db2..282a7872ad 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1,5 +1,5 @@ @@ -226,7 +226,9 @@ where from_item can be one of: this single SELECT command. Note that the sub-SELECT must be surrounded by parentheses, and an alias must be - provided for it. + provided for it. A + command + can also be used here. diff --git a/doc/src/sgml/ref/values.sgml b/doc/src/sgml/ref/values.sgml new file mode 100644 index 0000000000..9af66c387d --- /dev/null +++ b/doc/src/sgml/ref/values.sgml @@ -0,0 +1,244 @@ + + + + + VALUES + SQL - Language Statements + + + + VALUES + compute a set of rows + + + + VALUES + + + + +VALUES ( expression [, ...] ) [, ...] + [ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ] + [ LIMIT { count | ALL } ] + [ OFFSET start ] + + + + + Description + + + VALUES computes a row value or set of row values + specified by value expressions. It is most commonly used to generate + a constant table within a larger command, but it can be + used on its own. + + + + 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 UNION + (see ). + + + + Within larger commands, VALUES is syntactically allowed + anywhere that SELECT is. Because it is treated like a + SELECT by the grammar, it is possible to use the ORDER + BY, LIMIT, and OFFSET clauses with a + VALUES command. + + + + + Parameters + + + + expression + + + A constant or expression to compute and insert at the indicated place + in the resulting table (set of rows). In a VALUES list + appearing at the top level of an INSERT, an + expression can be replaced + by DEFAULT to indicate that the destination column's + default value should be inserted. DEFAULT cannot + be used when VALUES appears in other contexts. + + + + + + sort_expression + + + An expression or integer constant indicating how to sort the result + rows. This expression may refer to the columns of the + VALUES result as column1, column2, + etc. For more details see + . + + + + + + operator + + + A sorting operator. For details see + . + + + + + + count + + + The maximum number of rows to return. For details see + . + + + + + + start + + + The number of rows to skip before starting to return rows. + For details see + . + + + + + + + + Notes + + + VALUES lists with very large numbers of rows should be avoided, + as you may encounter out-of-memory failures or poor performance. + VALUES appearing within INSERT is a special case + (because the desired column types are known from the INSERT's + target table, and need not be inferred by scanning the VALUES + list), so it can handle larger lists than are practical in other contexts. + + + + + Examples + + + A bare VALUES command: + + +VALUES (1, 'one'), (2, 'two'), (3, 'three'); + + + This will return a table of two columns and three rows. It's effectively + equivalent to + + +SELECT 1 AS column1, 'one' AS column2 +UNION ALL +SELECT 2, 'two' +UNION ALL +SELECT 3, 'three'; + + + + + + More usually, VALUES is used within a larger SQL command. + The most common use is in INSERT: + + +INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); + + + + + In the context of INSERT, entries of a VALUES list + can be DEFAULT to indicate that the column default + should be used here instead of specifying a value: + + +INSERT INTO films VALUES + ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'), + ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT); + + + + + VALUES can also be used where a sub-SELECT might + be written, for example in a FROM clause: + + +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; + + + Note that an AS clause is required when VALUES + is used in a FROM clause, just as is true for + SELECT. It is not required that the AS clause + specify names for all the columns, but it's good practice to do so. + (The default column names for VALUES are column1, + column2, etc in PostgreSQL, but + these names might be different in other database systems.) + + + + When VALUES is used in INSERT, the values are all + automatically coerced to the datatype of the corresponding destination + column. When it's used in other contexts, it may be necessary to specify + the correct datatype. If the entries are all quoted literal constants, + coercing the first is sufficient to determine the assumed type for all: + + +SELECT * FROM machines +WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43')); + + + + + + For simple IN tests, it's better to rely on the + list-of-scalars form of IN than to write a VALUES + query as shown above. The list of scalars method requires less writing + and is often more efficient. + + + + + + Compatibility + + + VALUES conforms to the SQL standard, except that + LIMIT and OFFSET are + PostgreSQL extensions. + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index e90c45d162..64330928c4 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,4 +1,4 @@ - + Reference @@ -144,6 +144,7 @@ &unlisten; &update; &vacuum; + &values; diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index 1e9a407ac6..f4c521025d 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -1,4 +1,4 @@ - + Type Conversion @@ -799,6 +799,11 @@ padding spaces. + VALUES + determination of result type + + + GREATEST determination of result type @@ -814,8 +819,8 @@ types to become a single result set. The resolution algorithm is applied separately to each output column of a union query. The INTERSECT and EXCEPT constructs resolve dissimilar types in the same way as UNION. The -CASE, ARRAY, GREATEST and -LEAST constructs use the identical +CASE, ARRAY, VALUES, +GREATEST and LEAST constructs use the identical algorithm to match up their component expressions and select a result data type. -- 2.11.0