PHP小码农

Appearance

CREATE TABLE AS 与 SELECT INTO ​

CREATE TABLE AS（简称 CTAS）用一条 SELECT 的结果直接建表并灌数据，是建表 + 导数的合并写法。它常用于三件事：快速复制表结构、建立数据快照与归档、把复杂查询的结果物化成物理表。本节也讲清它与 SELECT INTO、VIEW、MATERIALIZED VIEW 的取舍。

前置知识

阅读本节前，建议先了解：删除表与清空数据（DROP TABLE / TRUNCATE）

基础概念 ​

先看四种"存查询结果"的方式对比，这是本节的总纲：

方式是否落地数据是否随源表变化能否建索引能否增量刷新典型用途
CREATE TABLE AS是否（快照）可以否，需自己写增量逻辑归档、快照、临时分析
SELECT INTO是否（快照）可以否同 CTAS，但不推荐
CREATE VIEW否是（每次查询重算）不能—简化复杂查询、权限隔离
CREATE MATERIALIZED VIEW是否，需 REFRESH可以支持 CONCURRENTLY 全量刷新报表缓存、昂贵的聚合

几个关键概念：

  • CTAS（CREATE TABLE AS）：CREATE TABLE name AS SELECT ...，用查询结果的列名与类型作为新表的列定义。
  • 类型推导（Type Inference）：新表列类型由 SELECT 输出表达式决定。例如 count(*) 推导为 bigint，sum(int) 推导为 bigint，avg(int) 推导为 numeric。
  • WITH NO DATA：只建表结构、不灌数据。用于"先建好结构、稍后再导入"。
  • 不继承约束与索引：CTAS 新表没有主键、唯一约束、外键、CHECK、默认值、索引——只有列名与类型（NOT NULL 在 PostgreSQL 会尽量保留，但不保证）。
  • SELECT INTO 是历史遗留语法：官方明确不推荐使用（容易与 PL/pgSQL 中的 SELECT ... INTO 变量 混淆），新代码应统一用 CTAS。

语法与用法 ​

text
-- ① CREATE TABLE AS
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]                       -- 可指定新列名
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

-- ② SELECT INTO（不推荐）
SELECT [ ... ] INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    FROM ... [WHERE ...]

最简可运行示例：

sql
-- 用查询结果直接建表
CREATE TABLE active_users AS
SELECT id, email FROM users WHERE status = 'active';
text
SELECT 2

注意返回的不是 CREATE TABLE 而是 SELECT n——CTAS 会告诉你灌入了多少行。

sql
-- 只建结构不灌数据
CREATE TABLE active_users_empty AS
SELECT id, email FROM users WHERE status = 'active'
WITH NO DATA;

-- 可自定义新表的列名
CREATE TABLE user_report (uid, mail) AS
SELECT id, email FROM users WHERE status = 'active';

详细说明 ​

类型推导：新表的列类型由谁决定 ​

CTAS 的列类型完全取决于查询输出，这一点在跨表搬运数据时至关重要：

sql
-- 观察类型推导结果
CREATE TABLE type_demo AS
SELECT
  count(*)                     AS cnt,        -- bigint
  sum(amount)                  AS total,      -- numeric（sum(numeric)）
  avg(amount)                  AS avg_amount, -- numeric
  min(created_at)              AS first_at,   -- timestamptz
  max(amount)::int             AS max_int,    -- integer（显式转换）
  now()                        AS snapshot_at,-- timestamptz
  'hello'                      AS literal,    -- text（unknown 会被解析为 text）
  NULL                         AS no_type     -- text（NULL 默认推导为 text）
FROM orders;
sql
-- 查看推导出的类型
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'type_demo'
ORDER BY ordinal_position;
text
 column_name |        data_type         | is_nullable
-------------+--------------------------+-------------
 cnt         | bigint                   | YES
 total       | numeric                  | YES
 avg_amount  | numeric                  | YES
 first_at    | timestamp with time zone | YES
 max_int     | integer                  | YES
 snapshot_at | timestamp with time zone | YES
 literal     | text                     | YES
 no_type     | text                     | YES
(8 rows)

sum/avg/count 的类型与直觉不同：sum(integer) 返回 bigint（防溢出），avg(integer) 返回 numeric（保留小数）。如果目标表列是 integer，CTAS 会得到 bigint，需要显式 ::integer 转换。

不继承的东西：约束、索引、默认值 ​

这是 CTAS 最容易踩的坑。新表只是一堆列，其他什么都没有：

sql
-- 源表有主键、唯一约束、默认值、索引
CREATE TABLE src (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code       text   NOT NULL UNIQUE,
  status     text   NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_src_status ON src (status);

-- CTAS 复制
CREATE TABLE dst AS SELECT * FROM src;
sql
-- 新表没有主键、没有唯一约束、没有索引、没有默认值
SELECT
  (SELECT count(*) FROM pg_index WHERE indrelid = 'dst'::regclass) AS index_count,
  (SELECT count(*) FROM pg_constraint WHERE conrelid = 'dst'::regclass) AS constraint_count;
text
 index_count | constraint_count
-------------+------------------
           0 |                0
(1 row)
text
-- \d dst 输出（注意：完全没有 Indexes 与 Check constraints 段）
                Table "public.dst"
   Column   |           Type           | Nullable | Default
------------+--------------------------+----------+---------
 id         | bigint                   | not null |
 code       | text                     | not null |
 status     | text                     | not null |
 created_at | timestamp with time zone | not null |

NOT NULL 是少数会被保留的约束（因为它是列属性而非独立约束对象，PostgreSQL 会沿类型推导一并带上）。但 IDENTITY 属性也不会复制，id 变成了普通 bigint not null。

若需要复刻结构，应该用 LIKE ... INCLUDING ALL，或用 CTAS 后再手工补约束：

sql
-- 方案一：复制结构（含索引与约束），不带数据
CREATE TABLE dst2 (LIKE src INCLUDING ALL);

-- 方案二：CTAS 后手工补
CREATE TABLE dst3 AS SELECT * FROM src WITH NO DATA;
ALTER TABLE dst3 ADD PRIMARY KEY (id);
ALTER TABLE dst3 ALTER COLUMN status SET DEFAULT 'active';
CREATE INDEX idx_dst3_status ON dst3 (status);

WITH NO DATA 与后续灌数 ​

sql
-- 先建结构
CREATE TABLE report_daily AS
SELECT date_trunc('day', created_at) AS stat_date, count(*) AS cnt
FROM orders
GROUP BY 1
WITH NO DATA;
text
SELECT 0
sql
-- 结构已就位但为空
SELECT count(*) FROM report_daily;
text
 count
-------
     0
(1 row)
sql
-- 稍后用 INSERT ... SELECT 灌数（可反复追加）
INSERT INTO report_daily
SELECT date_trunc('day', created_at), count(*)
FROM orders
GROUP BY 1;

-- 或先清空再重灌
TRUNCATE report_daily;
INSERT INTO report_daily
SELECT date_trunc('day', created_at), count(*)
FROM orders
GROUP BY 1;

CTAS 与 VIEW、MATERIALIZED VIEW 的取舍 ​

sql
-- VIEW：不落地，每次查询重算
CREATE VIEW v_active_users AS SELECT id, email FROM users WHERE status = 'active';

-- MATERIALIZED VIEW：落地，可用 CONCURRENTLY 刷新
CREATE MATERIALIZED VIEW mv_daily_stats AS
SELECT date_trunc('day', created_at) AS stat_date, count(*) AS cnt
FROM orders GROUP BY 1
WITH NO DATA;

REFRESH MATERIALIZED VIEW mv_daily_stats;

-- PostgreSQL 9.4+：并发刷新（需先建唯一索引）
CREATE UNIQUE INDEX uq_mv_daily_stats ON mv_daily_stats (stat_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_stats;

选型对照：

判断维度选 VIEW选 MATERIALIZED VIEW选 CTAS
数据新鲜度要求实时可接受延迟可接受延迟且不需自动刷新
查询代价每次都要重算，必须能接受只算一次，查询极快只算一次
是否需索引不需要（无实体）支持，常建唯一索引以并发刷新支持，需手工建
刷新机制天然最新REFRESH [CONCURRENTLY]无，需自己写重灌逻辑
是否随源表 DDL 变更依赖对象，源表列变动会报错需重建或刷新完全解耦，一张独立表
典型场景简化查询、列级权限控制报表加速、昂贵聚合归档、快照、ETL 中间表

CTAS 与物化视图的本质差异在于"自动刷新"：物化视图是数据库管理的对象，REFRESH 会重新执行定义好的查询；CTAS 表只是普通表，没有来源记录，刷新逻辑完全在应用侧。

SELECT INTO 为什么不推荐 ​

sql
-- 功能等价，但语法歧义大
SELECT id, email INTO active_users_bak FROM users WHERE status = 'active';
text
SELECT 2

官方文档明确列出三条理由：① SELECT ... INTO 在 PL/pgSQL 中是"把结果赋给变量"的含义，同一写法两种语义容易误读；② 不支持指定列名、存储参数、表空间等 CTAS 的全部选项；③ 它属于 PostgreSQL 历史遗留语法，SQL 标准中没有。

sql
-- 错误！在 PL/pgSQL 函数体中，这种写法是赋值而非建表
CREATE FUNCTION f() RETURNS text AS $$
DECLARE v_email text;
BEGIN
  SELECT email INTO v_email FROM users WHERE id = 1;   -- 这是赋值
  RETURN v_email;
END;
$$ LANGUAGE plpgsql;

实战示例 ​

场景：做一个"订单数据仓库"的日增量落地流程——① 每天把当天订单快照到归档表；② 建立结构化的月统计表；③ 用 CTAS 建中间表做 ETL，最后把结果合并进目标表。

第 1 步：准备环境

sql
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS users  CASCADE;
DROP TABLE IF EXISTS orders_archive, daily_sales_report, tmp_order_summary CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text   NOT NULL UNIQUE
);

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users (id),
  order_no   text   NOT NULL,
  status     text   NOT NULL DEFAULT 'paid',
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO users (email)
SELECT 'user' || id || '@example.com' FROM generate_series(1, 20) AS id;

INSERT INTO orders (user_id, order_no, amount, status, created_at)
SELECT (id % 20) + 1,
       'SO-' || lpad(id::text, 8, '0'),
       (random() * 2000 + 10)::numeric(12,2),
       CASE WHEN id % 10 = 0 THEN 'pending' ELSE 'paid' END,
       now() - (id % 90) * interval '1 day'
FROM generate_series(1, 50000) AS id;
text
CREATE TABLE
CREATE TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 20
INSERT 0 50000

第 2 步：CTAS 建当天快照（归档）

sql
-- 只归档 2026 年的已完成订单，并裁剪列
CREATE TABLE orders_archive AS
SELECT id, user_id, order_no, amount, created_at
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-01-01';

SELECT count(*) AS archived_rows FROM orders_archive;
text
SELECT 40000

 archived_rows
---------------
         40000
(1 row)

第 3 步：观察归档表缺什么

sql
-- 查看索引与约束数量
SELECT
  (SELECT count(*) FROM pg_index     WHERE indrelid = 'orders_archive'::regclass) AS idx,
  (SELECT count(*) FROM pg_constraint WHERE conrelid = 'orders_archive'::regclass) AS cons;
text
 idx | cons
-----+------
   0 |    0
(1 row)
sql
-- 归档表通常只需按时间过滤，补一个索引就够
CREATE INDEX idx_orders_archive_created_at ON orders_archive (created_at);

-- 但要注意：归档表没有主键，可能有重复数据，需要去重时自己处理
SELECT id, count(*) FROM orders_archive GROUP BY id HAVING count(*) > 1 LIMIT 5;
text
CREATE INDEX

 id | count
----+-------
(0 rows)

第 4 步：CTAS + WITH NO DATA 建日统计表骨架

sql
CREATE TABLE daily_sales_report (
  stat_date date          NOT NULL,
  order_count bigint      NOT NULL,
  total_amount numeric(16,2) NOT NULL
);

-- 也可以用 CTAS 直接得到类型骨架（列类型由推导决定）
CREATE TABLE daily_sales_report_ctas AS
SELECT
  created_at::date        AS stat_date,
  count(*)                AS order_count,
  sum(amount)             AS total_amount
FROM orders
GROUP BY created_at::date
WITH NO DATA;

-- 对比两种方式的类型
SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'daily_sales_report_ctas' ORDER BY ordinal_position;
text
CREATE TABLE
SELECT 0

 column_name  |        data_type
--------------+-------------------------
 stat_date    | date
 order_count  | bigint
 total_amount | numeric
(3 rows)

第 5 步：CTAS 做 ETL 中间表

sql
-- 单次查询算出用户维度汇总，落地成中间表
CREATE TEMP TABLE tmp_user_summary AS
SELECT
  o.user_id,
  u.email,
  count(*)          AS order_count,
  sum(o.amount)     AS total_amount,
  avg(o.amount)     AS avg_amount,
  min(o.created_at) AS first_order_at,
  max(o.created_at) AS last_order_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY o.user_id, u.email;
text
SELECT 20
sql
-- 中间表可以反复查询、加索引，不影响源表
SELECT user_id, email, order_count, total_amount
FROM tmp_user_summary
ORDER BY total_amount DESC
LIMIT 5;
text
 user_id |        email          | order_count | total_amount
---------+-----------------------+-------------+-------------
       7 | user7@example.com     |        2500 |    2512345.67
      13 | user13@example.com    |        2500 |    2501234.89
       3 | user3@example.com     |        2500 |    2498765.12
...
(5 rows)
sql
-- 中间表落地后加索引（CTAS 不会自动建）
CREATE INDEX idx_tmp_user_summary_total ON tmp_user_summary (total_amount DESC);

第 6 步：把结果合并进目标表

sql
-- 用 CTAS 生成的结果灌入正式报表表
INSERT INTO daily_sales_report (stat_date, order_count, total_amount)
SELECT created_at::date, count(*), sum(amount)
FROM orders
WHERE status = 'paid'
GROUP BY created_at::date
ON CONFLICT DO NOTHING;

SELECT * FROM daily_sales_report ORDER BY stat_date DESC LIMIT 5;
text
INSERT 0 90

  stat_date | order_count | total_amount
------------+-------------+--------------
 2026-09-24 |         1980 |   1980123.45
 2026-09-23 |         1960 |   1959876.12
 ...
(5 rows)

第 7 步：对比 CTAS 与 SELECT INTO

sql
-- CTAS：推荐写法，可指定列名与 WITH NO DATA
CREATE TABLE snap_ctas AS SELECT id, email FROM users WITH NO DATA;

-- SELECT INTO：等价但不推荐
SELECT id, email INTO snap_select_into FROM users WITH NO DATA;

-- 两者结构一致
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name IN ('snap_ctas', 'snap_select_into')
ORDER BY table_name, ordinal_position;
text
    table_name     | column_name | data_type
-------------------+-------------+-----------
 snap_ctas         | id          | bigint
 snap_ctas         | email       | text
 snap_select_into  | id          | bigint
 snap_select_into  | email       | text
(4 rows)

第 8 步：清理

sql
DROP TABLE IF EXISTS orders_archive, daily_sales_report, daily_sales_report_ctas,
                     snap_ctas, snap_select_into CASCADE;
DROP TABLE IF EXISTS orders, users CASCADE;
text
DROP TABLE
DROP TABLE

注意事项 ​

  • CTAS 不复制索引、主键、唯一约束、外键、默认值。 需要唯一性保障时必须手工补。
sql
-- 归档表没有主键，能插入重复 id
INSERT INTO orders_archive (id) VALUES (1);
INSERT INTO orders_archive (id) VALUES (1);   -- 成功！没有约束拦住
text
INSERT 0 1
INSERT 0 1
sql
-- 正确：手工补约束
CREATE TABLE orders_archive2 AS SELECT * FROM orders WITH NO DATA;
ALTER TABLE orders_archive2 ADD PRIMARY KEY (id);
  • CTAS 会全程持有源表的 ACCESS SHARE 锁。 简单的 CREATE TABLE AS SELECT 属于一个事务，长查询期间会阻塞源表上的 ALTER TABLE。
sql
-- 若希望锁时间短，可拆成两步
CREATE TABLE t AS SELECT 1 AS x WITH NO DATA;   -- 先建结构（秒完成）
-- 再另开会话灌数
INSERT INTO t SELECT id FROM huge_table;
  • CTAS 没有"原子可见性"优势。 PG 不支持 CREATE TABLE AS ... WITH NO DATA 后再加数据的"原子切换"；需要零切换的方案应使用 BEGIN; CREATE TABLE new_t AS SELECT ...; ALTER TABLE ... RENAME; COMMIT;。
sql
-- 原子发布模式
BEGIN;
CREATE TABLE orders_new AS SELECT * FROM orders_id_total;
ALTER TABLE orders RENAME TO orders_old;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

  • SELECT INTO 在 PL/pgSQL 中语义完全不同。 写函数体时务必分清。

  • CTAS 的列类型推导结果可能与源列不一致。 sum(int) → bigint、avg(int) → numeric、NULL → text。

sql
-- 需要保持原类型时必须显式转换
CREATE TABLE t AS SELECT id::bigint, total::numeric(16,2) FROM source;

  • WITH NO DATA 会返回 SELECT 0，容易被误判为"没执行成功"。

  • 归档表无约束意味着数据可能被反复写入。 增量归档要自己加"水印"条件（如 created_at > (SELECT max(created_at) FROM archive)）。

sql
-- 幂等增量归档
INSERT INTO orders_archive
SELECT * FROM orders
WHERE created_at > COALESCE((SELECT max(created_at) FROM orders_archive), '-infinity');
  • CTAS 的表没有来源记录，无法像物化视图那样知道"它来自哪条查询"。建议把定义 SQL 写进注释或迁移脚本。
sql
COMMENT ON TABLE orders_archive IS '订单归档表，来源：orders WHERE status=paid，按 created_at 增量';
  • TEMP 与 UNLOGGED 可组合使用。 临时表用于会话内中间计算，UNLOGGED 用于可重建的加速表（崩溃后会被清空）。
sql
CREATE TEMP TABLE tmp_calc AS SELECT ...;
CREATE UNLOGGED TABLE cache_table AS SELECT ...;
  • CTAS 建的表继承源表的表空间设定吗？ 不继承，除非显式写 TABLESPACE。

最佳实践 ​

  • 新代码统一用 CREATE TABLE AS，禁用 SELECT INTO。 后者是历史遗留语法，语义歧义大。
sql
-- 推荐
CREATE TABLE counter_snapshot AS SELECT sku, stock FROM products WHERE stock < 10;

-- 不推荐
SELECT sku, stock INTO counter_snapshot FROM products WHERE stock < 10;
  • 只建结构时固定加 WITH NO DATA，让意图显式，也避免大表灌数造成意外开销。
sql
CREATE TABLE report_base AS SELECT ... WITH NO DATA;
  • CTAS 后立刻补索引与约束，不要等到出问题才想起归档表没有主键。
sql
CREATE TABLE orders_archive AS SELECT * FROM orders WHERE created_at < now() - interval '1 year';
ALTER TABLE orders_archive ADD PRIMARY KEY (id);
CREATE INDEX idx_orders_archive_user ON orders_archive (user_id);
  • 需要定期刷新就用物化视图，不要用 CTAS 自己造轮子。
sql
CREATE MATERIALIZED VIEW mv_daily AS SELECT ... WITH NO DATA;
CREATE UNIQUE INDEX ON mv_daily (stat_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily;
  • ETL 中间表用 CREATE TEMP TABLE AS，会话结束自动清理，不给库留垃圾。
sql
CREATE TEMP TABLE tmp_stage AS SELECT ... FROM raw_source;
  • 归档表统一命名带日期后缀，便于按批次管理生命周期。
sql
CREATE TABLE orders_archive_2026h1 AS SELECT * FROM orders WHERE created_at < '2026-07-01';
  • 大表归档分批做，避免长事务与 WAL 峰值。
sql
-- 每批 10 万行，循环执行
CREATE TABLE orders_archive_2026h1 AS
SELECT * FROM orders WHERE created_at < '2026-07-01' LIMIT 100000;
  • 把 CTAS 的定义 SQL 记进表注释，方便后续维护者知道数据来源。
sql
COMMENT ON TABLE orders_archive_2026h1 IS
  $$来源：SELECT id, user_id, order_no, amount, created_at FROM orders WHERE status='paid' AND created_at < '2026-07-01'$$;
  • 切换类操作放进一个事务，用 RENAME 做原子替换。
sql
BEGIN;
CREATE TABLE products_new AS SELECT * FROM products WHERE is_active;
ALTER TABLE products     RENAME TO products_old;
ALTER TABLE products_new RENAME TO products;
COMMIT;
  • CTAS 落地前先 EXPLAIN 看代价，避免一个"顺手 CTAS"跑出十分钟的查询。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... FROM big_table JOIN ... GROUP BY ...;

下一节

继续学习：临时表与非日志表（TEMPORARY / UNLOGGED）

参考链接 ​