Postgresql 中文操作指南

7.7. VALUES 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 [, ...] ) [, ...]

每个由表达式括起来的列表在表中生成一行。所有列表都必须具有相同数量的元素（即表中的列数），并且每个列表中的相应条目必须具有兼容的数据类型。结果的每个列分配的实际数据类型使用与_UNION_相同的规则确定（见 Section 10.5）。

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 data types. The actual data type assigned to each column of the result is determined using the same rules as for UNION (see Section 10.5).

例如：

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';

默认情况下，PostgreSQL 为 VALUES 表的列分配 column1、column2 等名称。列名并非由 SQL 标准指定，不同的数据库系统执行此操作的方式也不同，因此通常最好使用表别名列表覆盖默认名称，如下所示：

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, like this:

=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
 num | letter
-----+--------
   1 | one
   2 | two
   3 | three
(3 rows)

在语法上，VALUES 后跟表达式列表被视为等效于：

Syntactically, VALUES followed by expression lists is treated as equivalent to:

SELECT select_list FROM table_expression

它可以出现在 SELECT 所在的任何位置。例如，您可以将它用作 UNION 的一部分，或为其附加一个 sort_specification (ORDER BY、LIMIT 和/或 OFFSET)。VALUES 最常用作 INSERT 命令中的数据源，其次是子查询。

and can appear anywhere a SELECT can. For example, you can use it as part 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.

有关更多信息，请参阅 VALUES 。

For more information see VALUES.