Postgresql 中文操作指南

SELECT INTO

SELECT INTO — 从查询结果中定义新表

SELECT INTO — define a new table from the results of a query

Synopsis

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

Description

SELECT INTO 创建一个新表并用查询计算出的数据填充它。与常规的 SELECT 不同，数据不会返回客户端。新表中的列具有与 SELECT 输出列关联的名称和数据类型。

SELECT INTO creates a new table and fills it with data computed by a query. The data is not returned to the client, as it is with a normal SELECT. The new table’s columns have the names and data types associated with the output columns of the SELECT.

Parameters

  • TEMPORARY or TEMP

    • If specified, the table is created as a temporary table. Refer to CREATE TABLE for details.

  • UNLOGGED

    • If specified, the table is created as an unlogged table. Refer to CREATE TABLE for details.

  • new_table

    • The name (optionally schema-qualified) of the table to be created.

所有其他参数在 SELECT 下进行了详细说明。

All other parameters are described in detail under SELECT.

Notes

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

CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset of the functionality provided by SELECT INTO.

与 CREATE TABLE AS 不同， SELECT INTO 不允许使用 USING _method or the table’s tablespace with TABLESPACE _tablespace_name. Use CREATE TABLE AS if necessary. Therefore, the default table access method is chosen for the new table. See default_table_access_method 指定表访问方法等属性，以了解更多信息。

In contrast to CREATE TABLE AS, SELECT INTO does not allow specifying properties like a table’s access method with USING _method or the table’s tablespace with _TABLESPACE _tablespace_name. Use _CREATE TABLE AS if necessary. Therefore, the default table access method is chosen for the new table. See default_table_access_method for more information.

Examples

创建新表 films_recent ，其中仅包含表 films 中的近期条目：

Create a new table films_recent consisting of only recent entries from the table films:

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

Compatibility

SQL标准使用 SELECT INTO 来表示将值选入主机程序的标量变量，而不是创建新表。这实际上是在ECPG（请参阅 Chapter 36 ）和PL/pgSQL（请参阅 Chapter 43 ）中发现的用法。PostgreSQL使用 SELECT INTO 来表示创建表的历史。其他一些SQL实现也以这种方式使用 SELECT INTO （但大多数SQL实现支持 CREATE TABLE AS ）。除了这些兼容性考虑因素外，最好在新代码中为此目的使用 CREATE TABLE AS 。

The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECPG (see Chapter 36) and PL/pgSQL (see Chapter 43). The PostgreSQL usage of SELECT INTO to represent table creation is historical. Some other SQL implementations also use SELECT INTO in this way (but most SQL implementations support CREATE TABLE AS instead). Apart from such compatibility considerations, it is best to use CREATE TABLE AS for this purpose in new code.

See Also

CREATE TABLE AS

CREATE TABLE AS