SELECT INTO

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

英文手册覆盖始于 PostgreSQL 6.4. 来源历史与运行验证各自保留独立版本边界。

当前阅读 PG 18·选择有来源记录的版本

6.4 6.5 7.0 7.1 7.2 7.3 7.4 8.0 8.1 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

anchor: SQL-SELECTINTO
file: sql-selectinto.html
lang: en
name: SELECT INTO
purpose: define a new table from the results of a query
purpose zh: 未知
sections same as: 未知
slug: 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 ] [...] ]

语法图

从左向右阅读；分支表示选择，旁路表示可选，循环表示重复。点击带下划线的参数可打开对应规则。

版本定义 PG 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 ] [...] ]

描述

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

比较版本

完整来源事实

synopsis html

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

synopsis text

[ 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 ] [...] ]

来源引用

完整定义与证据 JSON

定义来源

center · PostgreSQL 18 · a709ab85

正文语言: zh-Hans · 1b5ca64c