Postgres CREATE TABLE AS - 根据查询结果定义新表(完全复制表)

CREATE TABLE AS - 根据查询结果定义新表(完全复制表)

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

创建一个新表films_recent ，仅包含表影片中的最新条目：

CREATE TABLE films_recent AS
  SELECT * FROM films WHERE date_prod >= '2002-01-01';

要完全复制表，还可以使用使用TABLE命令的简短表单 ：

CREATE TABLE films2 AS
  TABLE films;

使用预准备语句创建一个新的临时表films_recent，其中仅包含表影片中的最近条目。新表有OID，将在提交时删除：

PREPARE recentfilms(date) AS
  SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
  EXECUTE recentfilms('2002-01-01');