↑↓ 选择 ↵ 打开 ⌫ 改范围 完整检索页

pgsql.cc 提供对 postgresql.org 官网内容的中文翻译，由 Pigsty 团队维护。

百科 / SQL 命令 / 查询与数据操作

SQL COMMAND · 查询与数据操作

SELECT INTO

根据查询结果定义一个新表

define a new table from the results of a query

SELECT查询与数据操作引入 6.4（基线）现存至 20 devel13 次语法变更

动词
SELECT
对象
INTO
引入版本
6.4（基线）
状态
现存
语法变更次数
13
手册小节数
6

本站手册 · 18官方文档 ↗

版本轨迹

相对 PostgreSQL 17 无变化。

语法铁道图 PostgreSQL 18

沿轨道从左向右阅读，分岔表示选择，绕行表示可选，回环表示重复。方框为参数，点击带下划线的参数可展开子规则。

WITH RECURSIVE with_query , SELECT ALL DISTINCT ON ( expression , ) * expression AS output_name , INTO TEMPORARY TEMP UNLOGGED TABLE new_table FROM from_item , WHERE condition GROUP BY expression , HAVING condition WINDOW window_name AS ( window_definition ) , UNION INTERSECT EXCEPT ALL DISTINCT select ORDER BY expression ASC DESC USING operator NULLS FIRST LAST , LIMIT count ALL OFFSET start ROW ROWS FETCH FIRST NEXT count ROW ROWS ONLY FOR UPDATE SHARE OF table_name , NOWAIT

语法概要

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
[ { * | expression [ [ AS ] output_name ] } [, ...] ]
INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition ]
[ WINDOW window_name AS ( window_definition ) [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
[ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
[ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

PostgreSQL 18 手册 · 查看完整参考页

描述

SELECT INTO 创建一个新表，并用查询计算得到的数据填充该表。与普通的 SELECT 不同，这些数据不会返回给客户端。新表的列具有与 SELECT 输出列对应的名称和数据类型。

参数

TEMPORARY 或 TEMP

如果指定，将把该表创建为临时表。详见 CREATE TABLE。

UNLOGGED

如果指定，该表将创建为不记录 WAL 的表。详见 CREATE TABLE。

new_table

要创建的表名（可选地带模式限定）。

所有其他参数都在 SELECT 中有详细说明。

注解

CREATE TABLE AS 在功能上与 SELECT INTO 类似。CREATE TABLE AS 是推荐使用的语法，因为这种形式的 SELECT INTO 在 ECPG 或 PL/pgSQL 中不可用，因为它们对 INTO 子句有不同的解释。此外，CREATE TABLE AS 提供的功能是 SELECT INTO 所提供功能的超集。

与 CREATE TABLE AS 不同，SELECT INTO 不允许使用 USING method 指定诸如表访问方法之类的属性，也不允许使用 TABLESPACE tablespace_name 指定表空间。如有需要，请使用 CREATE TABLE AS。因此，新表会选用默认的表访问方法。更多信息请参见 default_table_access_method。

示例

创建一个新表 films_recent，它只包含表 films 中的最近条目： 

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

兼容性

SQL 标准使用 SELECT INTO 表示将值选入宿主程序的标量变量，而不是创建一个新表。这确实是 ECPG（见第 34 章）和 PL/pgSQL（见第 41 章）中的用法。PostgreSQL 使用 SELECT INTO 表示创建表则是历史遗留用法。其他一些 SQL 实现也以这种方式使用 SELECT INTO（但大多数 SQL 实现改为支持 CREATE TABLE AS）。撇开这类兼容性考虑，对于新代码，最好为此目的使用 CREATE TABLE AS。

另见

CREATE TABLE AS

语法演化

相邻大版本之间的差异，新的在前。版本号链接到对应快照。

  1. PostgreSQL 19← 18语法变化

    + [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]+ [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]+ [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF from_reference [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]− [ GROUP BY expression [, ...] ]− [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

  2. PostgreSQL 17← 16正文更新

    正文更新

  3. PostgreSQL 14← 13正文更新

    正文更新

  4. PostgreSQL 12← 11语法变化

    + [ { * | expression [ [ AS ] output_name ] } [, ...] ]正文更新

  5. PostgreSQL 11← 10正文更新

    正文更新

  6. PostgreSQL 10← 9.6正文更新

    正文更新

  7. PostgreSQL 9.6← 9.5正文更新

    正文更新

  8. PostgreSQL 9.5← 9.4语法变化

    − [ HAVING condition [, ...] ]

  9. PostgreSQL 9.4← 9.3正文更新

    正文更新

  10. PostgreSQL 9.3← 9.2正文更新

    正文更新

  11. PostgreSQL 9.1← 9.0语法变化

    + INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table+ [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]正文更新

  12. PostgreSQL 9.0← 8.4正文更新

    正文更新

  13. PostgreSQL 8.4← 8.3语法变化

    + [ WITH [ RECURSIVE ] with_query [, ...] ]+ * | expression [ [ AS ] output_name ] [, ...]+ [ HAVING condition [, ...] ]+ [ WINDOW window_name AS ( window_definition ) [, ...] ]+ [ OFFSET start [ ROW | ROWS ] ]+ [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]

  14. PostgreSQL 8.3← 8.2语法变化

    + [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]正文更新

  15. PostgreSQL 8.2← 8.1语法变化

    + [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]正文更新

  16. PostgreSQL 8.1← 8.0语法变化

    + [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] ]− [ FOR UPDATE [ OF tablename [, ...] ] ]正文更新

  17. PostgreSQL 8.0← 7.4正文更新

    正文更新

  18. PostgreSQL 7.4← 7.3正文更新

    正文更新

  19. PostgreSQL 7.3← 7.2语法变化

    + [ LIMIT { count | ALL } ]+ [ OFFSET start ]+ [ FOR UPDATE [ OF tablename [, ...] ] ]− [ LIMIT [ start , ] { count | ALL } ]− [ OFFSET start ]− where from_item can be:− [ ONLY ] table_name [ * ]− [ [ AS ] alias [ ( column_alias_list ) ] ]− |− ( select )− [ AS ] alias [ ( column_alias_list ) ]− |− from_item [ NATURAL ] join_type from_item− [ ON join_condition | USING ( join_column_list ) ]正文更新

  20. PostgreSQL 7.2← 7.1语法变化

    + [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]+ [ LIMIT [ start , ] { count | ALL } ]− [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]− [ LIMIT { count | ALL } [ { OFFSET | , } start ]]正文更新

  21. PostgreSQL 7.1← 7.0语法变化

    sql-selectinto.htm → sql-selectinto.html

    + * | expression [ AS output_name ] [, ...]+ [ FROM from_item [, ...] ]+ [ GROUP BY expression [, ...] ]+ [ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]+ [ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]+ [ FOR UPDATE [ OF tablename [, ...] ] ]+ [ LIMIT { count | ALL } [ { OFFSET | , } start ]]+ where from_item can be:+ [ ONLY ] table_name [ * ]+ [ [ AS ] alias [ ( column_alias_list ) ] ]+ |+ ( select )+ [ AS ] alias [ ( column_alias_list ) ]+ |+ from_item [ NATURAL ] join_type from_item+ [ ON join_condition | USING ( join_column_list ) ]− expression [ AS name ] [, ...]− [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]− [ FROM table [ alias ] [, ...] ]− [ GROUP BY column [, ...] ]− [ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]− [ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]− [ FOR UPDATE [ OF class_name [, ...] ] ]正文更新

  22. PostgreSQL 7.0← 6.5语法变化

    + SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]+ [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]+ [ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]+ [ FOR UPDATE [ OF class_name [, ...] ] ]+ LIMIT { count | ALL } [ { OFFSET | , } start ]− [ LIMIT count [OFFSET|, count]]

  23. PostgreSQL 6.5← 6.4语法变化

    + INTO [TEMP] [ TABLE ] new_table ]+ [ { UNION [ALL] | INTERSECT | EXCEPT } select]+ [ FOR UPDATE [OF class_name...]]+ [ LIMIT count [OFFSET|, count]]

同组命令

命令动词对象版本变动最近变更
QUERIES & DATA查询与数据操作10 条↑
COPYCOPY— 1917 次
在文件和表之间复制数据现存
DELETEDELETE— 1811 次
删除表中的行现存
EXPLAINEXPLAIN— 1910 次
显示一个语句的执行计划现存
INSERTINSERT— 1912 次
在表中插入新行现存
LOCKLOCK— 9.07 次
锁定表现存
MERGEMERGE— 182 次
有条件地插入、更新或删除表中的行现存
SELECTSELECT— 1722 次
从表或视图中检索行现存
UPDATEUPDATE— 1813 次
更新表中的行现存
VALUESVALUES— 8.41 次
计算一组行现存
SELECT INTOSELECTINTO 1913 次
根据查询结果定义一个新表现存