{"Entry":{"collection":"sql","key":"values","name":"VALUES","aliases":["values"],"metadata":{"aliases":["values"],"changed_in":["8.4"],"changes":[{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"8.3"},{"from":"8.3","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[ OFFSET start [ ROW | ROWS ] ]","[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]"],"removed":[]},"to":"8.4"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples"],"removed":[]},"status":"changed","synopsis":null,"to":"15"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["examples"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"1cc63d73008300d643463bf161344c8529ab3a685b22f3cdfa42e745cba02fd1","editorial":{},"first_version":"8.2","group":"query","imported_at":"2026-09-30T17:43:39.10348+08:00","last_version":"20","name":"VALUES","object":"","position":11008,"present_in":["8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"compute a set of rows","purpose_zh":"","related":["insert","select"],"slug":"values","source_rev":"a709ab85","synopsis":"VALUES ( expression [, ...] ) [, ...]\n[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]","verb":"VALUES"}},"Definition":{"Collection":"sql","Key":"values","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"values","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-VALUES","file":"sql-values.html","lang":"en","name":"VALUES","purpose":"compute a set of rows","purpose_zh":"","related":["insert","select"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e computes a row value or set of row values specified by value expressions. It is most commonly used to generate a \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003econstant table\u003c/span\u003e”\u003c/span\u003e within a larger command, but it can be used on its own.\u003c/p\u003e\u003cp\u003eWhen 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 \u003ccode class=\"literal\"\u003eUNION\u003c/code\u003e (see \u003ca href=\"/docs/18/typeconv-union-case.html\" title=\"10.5. UNION, CASE, and Related Constructs\"\u003eSection 10.5\u003c/a\u003e).\u003c/p\u003e\u003cp\u003eWithin larger commands, \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e is syntactically allowed anywhere that \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e is. Because it is treated like a \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e by the grammar, it is possible to use the \u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e, \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e (or equivalently \u003ccode class=\"literal\"\u003eFETCH FIRST\u003c/code\u003e), and \u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e clauses with a \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e command.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA constant or expression to compute and insert at the indicated place in the resulting table (set of rows). In a \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e list appearing at the top level of an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, an \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e can be replaced by \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e to indicate that the destination column's default value should be inserted. \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e cannot be used when \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e appears in other contexts.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eAn expression or integer constant indicating how to sort the result rows. This expression can refer to the columns of the \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e result as \u003ccode class=\"literal\"\u003ecolumn1\u003c/code\u003e, \u003ccode class=\"literal\"\u003ecolumn2\u003c/code\u003e, etc. For more details see \u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY Clause\"\u003eORDER BY Clause\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoperator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eA sorting operator. For details see \u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY Clause\"\u003eORDER BY Clause\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe maximum number of rows to return. For details see \u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT Clause\"\u003eLIMIT Clause\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe number of rows to skip before starting to return rows. For details see \u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT Clause\"\u003eLIMIT Clause\u003c/a\u003e in the \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e documentation.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e lists with very large numbers of rows should be avoided, as you might encounter out-of-memory failures or poor performance. \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e appearing within \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e is a special case (because the desired column types are known from the \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e's target table, and need not be inferred by scanning the \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e list), so it can handle larger lists than are practical in other contexts.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eA bare \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e command:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eVALUES (1, 'one'), (2, 'two'), (3, 'three');\n\u003c/pre\u003e\u003cp\u003eThis will return a table of two columns and three rows. It's effectively equivalent to:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT 1 AS column1, 'one' AS column2\nUNION ALL\nSELECT 2, 'two'\nUNION ALL\nSELECT 3, 'three';\n\u003c/pre\u003e\u003cp\u003eMore usually, \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e is used within a larger SQL command. The most common use is in \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');\n\u003c/pre\u003e\u003cp\u003eIn the context of \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, entries of a \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e list can be \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e to indicate that the column default should be used here instead of specifying a value:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),\n    ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);\n\u003c/pre\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e can also be used where a sub-\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e might be written, for example in a \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e clause:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT f.*\n  FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)\n  WHERE f.studio = t.studio AND f.kind = t.kind;\n\nUPDATE employees SET salary = salary * v.increase\n  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)\n  WHERE employees.depno = v.depno AND employees.sales \u0026gt;= v.target;\n\u003c/pre\u003e\u003cp\u003eNote that an \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e clause is required when \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e is used in a \u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e clause, just as is true for \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e. It is not required that the \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e clause specify names for all the columns, but it's good practice to do so. (The default column names for \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e are \u003ccode class=\"literal\"\u003ecolumn1\u003c/code\u003e, \u003ccode class=\"literal\"\u003ecolumn2\u003c/code\u003e, etc. in \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e, but these names might be different in other database systems.)\u003c/p\u003e\u003cp\u003eWhen \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e is used in \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, 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:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT * FROM machines\nWHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));\n\u003c/pre\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003eTip\u003c/h3\u003e\u003cp\u003eFor simple \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e tests, it's better to rely on the \u003ca href=\"/docs/18/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR\" title=\"9.25.1. IN\"\u003elist-of-scalars\u003c/a\u003e form of \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e than to write a \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e query as shown above. The list of scalars method requires less writing and is often more efficient.\u003c/p\u003e\u003c/div\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e conforms to the SQL standard. \u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e and \u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e are \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extensions; see also under \u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/insert/?v=18\" title=\"INSERT\"\u003e\u003cspan class=\"refentrytitle\"\u003eINSERT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"VALUES ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e [, ...] ) [, ...]\n    [ ORDER BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_expression\u003c/code\u003e\u003c/em\u003e [ ASC | DESC | USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoperator\u003c/code\u003e\u003c/em\u003e ] [, ...] ]\n    [ LIMIT { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e | ALL } ]\n    [ OFFSET \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e [ ROW | ROWS ] ]\n    [ FETCH { FIRST | NEXT } [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e ] { ROW | ROWS } ONLY ]","synopsis_text":"VALUES ( expression [, ...] ) [, ...]\n[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"values","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"VALUES","Summary":"计算一组行","BodyHTML":"\u003cpre\u003eVALUES ( expression [, ...] ) [, ...]\n[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eVALUES\u003c/code\u003e计算由值表达式指定的一个行值或一组行值。它最常用于在更大的命令中生成一个\u003cspan\u003e“\u003cspan\u003e常量表\u003c/span\u003e”\u003c/span\u003e，但也可以单独使用。\u003c/p\u003e\u003cp\u003e当指定多于一行时，所有行都必须具有相同数量的元素。结果表各列的数据类型由出现在该列中的表达式的显式类型或推断类型组合决定，所用规则与 \u003ccode\u003eUNION\u003c/code\u003e相同（见\u003ca href=\"/docs/18/typeconv-union-case.html\" rel=\"nofollow\"\u003e第 10.5 节\u003c/a\u003e）。\u003c/p\u003e\u003cp\u003e在更大的命令中，从语法上说，凡是允许出现\u003ccode\u003eSELECT\u003c/code\u003e的地方，也都允许出现\u003ccode\u003eVALUES\u003c/code\u003e。因为在语法中它被当作 \u003ccode\u003eSELECT\u003c/code\u003e处理，所以可以对\u003ccode\u003eVALUES\u003c/code\u003e 命令使用\u003ccode\u003eORDER BY\u003c/code\u003e、\u003ccode\u003eLIMIT\u003c/code\u003e（或者等效的\u003ccode\u003eFETCH FIRST\u003c/code\u003e）以及\u003ccode\u003eOFFSET\u003c/code\u003e子句。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要在结果表（行集合）中相应位置计算并插入的常量或表达式。在出现在\u003ccode\u003eINSERT\u003c/code\u003e顶层的 \u003ccode\u003eVALUES\u003c/code\u003e列表中，\u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e可以用 \u003ccode\u003eDEFAULT\u003c/code\u003e替代，以表示应插入目标列的默认值。当\u003ccode\u003eVALUES\u003c/code\u003e出现在其他上下文中时，不能使用 \u003ccode\u003eDEFAULT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esort_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个指示结果行如何排序的表达式或整数常量。该表达式可以将\u003ccode\u003eVALUES\u003c/code\u003e结果的列引用为\u003ccode\u003ecolumn1\u003c/code\u003e、\u003ccode\u003ecolumn2\u003c/code\u003e等。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY 子句\" rel=\"nofollow\"\u003eORDER BY Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eoperator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个排序操作符。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY 子句\" rel=\"nofollow\"\u003eORDER BY Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e返回的最大行数。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT 子句\" rel=\"nofollow\"\u003eLIMIT Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e开始返回行之前要跳过的行数。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT 子句\" rel=\"nofollow\"\u003eLIMIT Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e应避免使用行数非常多的\u003ccode\u003eVALUES\u003c/code\u003e列表，因为这可能导致内存不足错误或性能不佳。出现在\u003ccode\u003eINSERT\u003c/code\u003e中的 \u003ccode\u003eVALUES\u003c/code\u003e属于一种特殊情况（因为所需的列类型可以从\u003ccode\u003eINSERT\u003c/code\u003e的目标表得知，并且无须通过扫描 \u003ccode\u003eVALUES\u003c/code\u003e列表来推断），因此它能处理比其他上下文中实际可行的更大的列表。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e一个独立的\u003ccode\u003eVALUES\u003c/code\u003e命令：\u003c/p\u003e\u003cpre\u003eVALUES (1, \u0026#39;one\u0026#39;), (2, \u0026#39;two\u0026#39;), (3, \u0026#39;three\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e这将返回一个两列三行的表。它实际上等效于：\u003c/p\u003e\u003cpre\u003eSELECT 1 AS column1, \u0026#39;one\u0026#39; AS column2\nUNION ALL\nSELECT 2, \u0026#39;two\u0026#39;\nUNION ALL\nSELECT 3, \u0026#39;three\u0026#39;;\n\u003c/pre\u003e\u003cp\u003e更常见的是，\u003ccode\u003eVALUES\u003c/code\u003e会用在更大的 SQL 命令中。最常见的用法是在\u003ccode\u003eINSERT\u003c/code\u003e中：\u003c/p\u003e\u003cpre\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES (\u0026#39;T_601\u0026#39;, \u0026#39;Yojimbo\u0026#39;, 106, \u0026#39;1961-06-16\u0026#39;, \u0026#39;Drama\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e在\u003ccode\u003eINSERT\u003c/code\u003e的上下文中，\u003ccode\u003eVALUES\u003c/code\u003e列表中的项可以写成\u003ccode\u003eDEFAULT\u003c/code\u003e，表示此处应使用该列的默认值，而不是显式指定一个值：\u003c/p\u003e\u003cpre\u003eINSERT INTO films VALUES\n    (\u0026#39;UA502\u0026#39;, \u0026#39;Bananas\u0026#39;, 105, DEFAULT, \u0026#39;Comedy\u0026#39;, \u0026#39;82 minutes\u0026#39;),\n    (\u0026#39;T_601\u0026#39;, \u0026#39;Yojimbo\u0026#39;, 106, DEFAULT, \u0026#39;Drama\u0026#39;, DEFAULT);\n\u003c/pre\u003e\u003cp\u003e\u003ccode\u003eVALUES\u003c/code\u003e也可以用在原本可以编写子\u003ccode\u003eSELECT\u003c/code\u003e的地方，例如在\u003ccode\u003eFROM\u003c/code\u003e子句中：\u003c/p\u003e\u003cpre\u003eSELECT f.*\n  FROM films f, (VALUES(\u0026#39;MGM\u0026#39;, \u0026#39;Horror\u0026#39;), (\u0026#39;UA\u0026#39;, \u0026#39;Sci-Fi\u0026#39;)) AS t (studio, kind)\n  WHERE f.studio = t.studio AND f.kind = t.kind;\n\nUPDATE employees SET salary = salary * v.increase\n  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)\n  WHERE employees.depno = v.depno AND employees.sales \u0026gt;= v.target;\n\u003c/pre\u003e\u003cp\u003e注意，在\u003ccode\u003eFROM\u003c/code\u003e子句中使用\u003ccode\u003eVALUES\u003c/code\u003e时，需要有一个\u003ccode\u003eAS\u003c/code\u003e子句，这一点与\u003ccode\u003eSELECT\u003c/code\u003e相同。虽然\u003ccode\u003eAS\u003c/code\u003e子句不要求为所有列都指定名称，但这样做是一个好习惯。（在\u003cspan\u003ePostgreSQL\u003c/span\u003e中，\u003ccode\u003eVALUES\u003c/code\u003e的默认列名是 \u003ccode\u003ecolumn1\u003c/code\u003e、\u003ccode\u003ecolumn2\u003c/code\u003e等，但在其他数据库系统中，这些名称可能不同。）\u003c/p\u003e\u003cp\u003e当在\u003ccode\u003eINSERT\u003c/code\u003e中使用\u003ccode\u003eVALUES\u003c/code\u003e时，所有值都会自动转换为相应目标列的数据类型。当它用于其他上下文时，可能有必要指定正确的数据类型。如果各项都是带引号的字符串常量，只需转换第一项，就足以为所有项确定假定的数据类型：\u003c/p\u003e\u003cpre\u003eSELECT * FROM machines\nWHERE ip_address IN (VALUES(\u0026#39;192.168.0.1\u0026#39;::inet), (\u0026#39;192.168.0.10\u0026#39;), (\u0026#39;192.168.1.43\u0026#39;));\n\u003c/pre\u003e\u003cdiv\u003e\u003ch3\u003e提示\u003c/h3\u003e\u003cp\u003e对于简单的\u003ccode\u003eIN\u003c/code\u003e测试，最好使用\u003ccode\u003eIN\u003c/code\u003e的\u003ca href=\"/docs/18/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR\" rel=\"nofollow\"\u003e标量列表\u003c/a\u003e形式，而不是写一个像上面那样的\u003ccode\u003eVALUES\u003c/code\u003e查询。标量列表方法写法更简洁，而且通常效率更高。\u003c/p\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eVALUES\u003c/code\u003e符合 SQL 标准。\u003ccode\u003eLIMIT\u003c/code\u003e和\u003ccode\u003eOFFSET\u003c/code\u003e是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展，另见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/insert/?v=18\" title=\"INSERT\" rel=\"nofollow\"\u003e\u003cspan\u003eINSERT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\" rel=\"nofollow\"\u003e\u003cspan\u003eSELECT\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"5d1f9e265d4c4870877e81426f9d65987fd71f7f20ce12ac8bd4629710792965","Payload":{"purpose_zh":"计算一组行","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e计算由值表达式指定的一个行值或一组行值。它最常用于在更大的命令中生成一个\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e常量表\u003c/span\u003e”\u003c/span\u003e，但也可以单独使用。\u003c/p\u003e\u003cp\u003e当指定多于一行时，所有行都必须具有相同数量的元素。结果表各列的数据类型由出现在该列中的表达式的显式类型或推断类型组合决定，所用规则与 \u003ccode class=\"literal\"\u003eUNION\u003c/code\u003e相同（见\u003ca href=\"/docs/18/typeconv-union-case.html\" title=\"10.5. UNION、CASE及相关结构\"\u003e第 10.5 节\u003c/a\u003e）。\u003c/p\u003e\u003cp\u003e在更大的命令中，从语法上说，凡是允许出现\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e的地方，也都允许出现\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e。因为在语法中它被当作 \u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e处理，所以可以对\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e 命令使用\u003ccode class=\"literal\"\u003eORDER BY\u003c/code\u003e、\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e（或者等效的\u003ccode class=\"literal\"\u003eFETCH FIRST\u003c/code\u003e）以及\u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e子句。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要在结果表（行集合）中相应位置计算并插入的常量或表达式。在出现在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e顶层的 \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e列表中，\u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e可以用 \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e替代，以表示应插入目标列的默认值。当\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e出现在其他上下文中时，不能使用 \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_expression\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个指示结果行如何排序的表达式或整数常量。该表达式可以将\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e结果的列引用为\u003ccode class=\"literal\"\u003ecolumn1\u003c/code\u003e、\u003ccode class=\"literal\"\u003ecolumn2\u003c/code\u003e等。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY 子句\"\u003eORDER BY Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eoperator\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e一个排序操作符。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-ORDERBY\" title=\"ORDER BY 子句\"\u003eORDER BY Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e返回的最大行数。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT 子句\"\u003eLIMIT Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e开始返回行之前要跳过的行数。详细信息参见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e文档中的\u003ca href=\"/docs/18/sql-select.html#SQL-LIMIT\" title=\"LIMIT 子句\"\u003eLIMIT Clause\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e应避免使用行数非常多的\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e列表，因为这可能导致内存不足错误或性能不佳。出现在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e中的 \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e属于一种特殊情况（因为所需的列类型可以从\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e的目标表得知，并且无须通过扫描 \u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e列表来推断），因此它能处理比其他上下文中实际可行的更大的列表。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e一个独立的\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e命令：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eVALUES (1, 'one'), (2, 'two'), (3, 'three');\n\u003c/pre\u003e\u003cp\u003e这将返回一个两列三行的表。它实际上等效于：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT 1 AS column1, 'one' AS column2\nUNION ALL\nSELECT 2, 'two'\nUNION ALL\nSELECT 3, 'three';\n\u003c/pre\u003e\u003cp\u003e更常见的是，\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e会用在更大的 SQL 命令中。最常见的用法是在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e中：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films (code, title, did, date_prod, kind)\n    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');\n\u003c/pre\u003e\u003cp\u003e在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e的上下文中，\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e列表中的项可以写成\u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e，表示此处应使用该列的默认值，而不是显式指定一个值：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eINSERT INTO films VALUES\n    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'),\n    ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama', DEFAULT);\n\u003c/pre\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e也可以用在原本可以编写子\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e的地方，例如在\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e子句中：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT f.*\n  FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)\n  WHERE f.studio = t.studio AND f.kind = t.kind;\n\nUPDATE employees SET salary = salary * v.increase\n  FROM (VALUES(1, 200000, 1.2), (2, 400000, 1.4)) AS v (depno, target, increase)\n  WHERE employees.depno = v.depno AND employees.sales \u0026gt;= v.target;\n\u003c/pre\u003e\u003cp\u003e注意，在\u003ccode class=\"literal\"\u003eFROM\u003c/code\u003e子句中使用\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e时，需要有一个\u003ccode class=\"literal\"\u003eAS\u003c/code\u003e子句，这一点与\u003ccode class=\"command\"\u003eSELECT\u003c/code\u003e相同。虽然\u003ccode class=\"literal\"\u003eAS\u003c/code\u003e子句不要求为所有列都指定名称，但这样做是一个好习惯。（在\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e中，\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e的默认列名是 \u003ccode class=\"literal\"\u003ecolumn1\u003c/code\u003e、\u003ccode class=\"literal\"\u003ecolumn2\u003c/code\u003e等，但在其他数据库系统中，这些名称可能不同。）\u003c/p\u003e\u003cp\u003e当在\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e中使用\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e时，所有值都会自动转换为相应目标列的数据类型。当它用于其他上下文时，可能有必要指定正确的数据类型。如果各项都是带引号的字符串常量，只需转换第一项，就足以为所有项确定假定的数据类型：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eSELECT * FROM machines\nWHERE ip_address IN (VALUES('192.168.0.1'::inet), ('192.168.0.10'), ('192.168.1.43'));\n\u003c/pre\u003e\u003cdiv class=\"tip\"\u003e\u003ch3\u003e提示\u003c/h3\u003e\u003cp\u003e对于简单的\u003ccode class=\"literal\"\u003eIN\u003c/code\u003e测试，最好使用\u003ccode class=\"literal\"\u003eIN\u003c/code\u003e的\u003ca href=\"/docs/18/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR\" title=\"9.25.1. IN\"\u003e标量列表\u003c/a\u003e形式，而不是写一个像上面那样的\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e查询。标量列表方法写法更简洁，而且通常效率更高。\u003c/p\u003e\u003c/div\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eVALUES\u003c/code\u003e符合 SQL 标准。\u003ccode class=\"literal\"\u003eLIMIT\u003c/code\u003e和\u003ccode class=\"literal\"\u003eOFFSET\u003c/code\u003e是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展，另见\u003ca href=\"/docs/18/sql-select.html\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/insert/?v=18\" title=\"INSERT\"\u003e\u003cspan class=\"refentrytitle\"\u003eINSERT\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/select/?v=18\" title=\"SELECT\"\u003e\u003cspan class=\"refentrytitle\"\u003eSELECT\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"VALUES ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e [, ...] ) [, ...]\n    [ ORDER BY \u003cem class=\"replaceable\"\u003e\u003ccode\u003esort_expression\u003c/code\u003e\u003c/em\u003e [ ASC | DESC | USING \u003cem class=\"replaceable\"\u003e\u003ccode\u003eoperator\u003c/code\u003e\u003c/em\u003e ] [, ...] ]\n    [ LIMIT { \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e | ALL } ]\n    [ OFFSET \u003cem class=\"replaceable\"\u003e\u003ccode\u003estart\u003c/code\u003e\u003c/em\u003e [ ROW | ROWS ] ]\n    [ FETCH { FIRST | NEXT } [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecount\u003c/code\u003e\u003c/em\u003e ] { ROW | ROWS } ONLY ]","synopsis_text":"VALUES ( expression [, ...] ) [, ...]\n[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [, ...] ]\n[ LIMIT { count | ALL } ]\n[ OFFSET start [ ROW | ROWS ] ]\n[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}