Postgresql 中文操作指南
Synopsis
VALUES ( expression [, ...] ) [, ...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]Description
VALUES 计算由值表达式指定的行值或行值集合。它最常用于在更大的命令内生成“常量表”，但它可以自行使用。
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.
当指定超过一行时，所有行都必须具有相同数量的元素。结果表列的数据类型是由结合该列中出现的表达式的显式或推断类型决定的，采用与 UNION 相同的规则（参见 Section 10.5 ）。
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 Section 10.5).
在较大的命令中， VALUES 在语法上允许出现在 SELECT 允许出现的任何位置。因为它在语法上被视为 SELECT ，所以可以使用 ORDER BY 、 LIMIT （或等同的 FETCH FIRST ）和 OFFSET 子句与 VALUES 命令配合使用。
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 (or equivalently FETCH FIRST), 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 can refer to the columns of the VALUES result as column1, column2, etc. For more details see ORDER BY Clause in the SELECT documentation.
-
-
operator
-
A sorting operator. For details see ORDER BY Clause in the SELECT documentation.
-
-
count
-
The maximum number of rows to return. For details see LIMIT Clause in the SELECT documentation.
-
-
start
-
The number of rows to skip before starting to return rows. For details see LIMIT Clause in the SELECT documentation.
-
Notes
VALUES 列表与行数极大时应避免，因为你可能会遇到内存不足故障或性能不佳。出现在 INSERT 中的 VALUES 是一个特例（因为所需列类型已从 INSERT 的目标表中得知，并且无需通过扫描 VALUES 列表来推断），因此它可以处理比其他上下文中实际处理更大的列表。
VALUES lists with very large numbers of rows should be avoided, as you might 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
一个不带参数的 VALUES 命令：
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';VALUES 通常在较大的 SQL 命令中使用。最常见的用途是在 INSERT 中：
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');在 INSERT 的上下文中， VALUES 列表的条目可以 DEFAULT ，以指示应在此处使用列的默认值，而不要指定值：
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);能够在可能会写入子 SELECT 的地方使用 VALUES ，例如在 FROM 子句中：
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;请注意，当 VALUES 用在 FROM 子句中时，需要 AS 子句，就像 SELECT 中那样。 AS 子句不要求为所有列指定名称，但最好这样做。（ VALUES 的默认列名称在 PostgreSQL 中是 column1 、 column2 等，但这些名称在其他数据库系统中可能不同。）
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.)
当 VALUES 用在 INSERT 中时，所有值会自动强制转换为相应目标列的数据类型。在其他上下文中使用它时，可能需要指定正确的数据类型。如果所有条目都是带引号的文字常量，强制转换第一个条目就足以确定所有条目的假定类型：
When VALUES is used in INSERT, 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:
SELECT * FROM machines
WHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));Tip
对于简单的 IN 测试，依靠 IN 的 list-of-scalars 形式比编写上述 VALUES 查询更好。标量方法列表所需编写内容较少，通常效率更高。
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.