PostgreSQL CREATE

先区分两个概念：

CRUD 中的 Create：新增数据，对应 INSERT。
PostgreSQL 的 CREATE：创建数据库对象，属于 DDL，例如创建数据库、表、索引、视图和函数。

一、CREATE 可以创建什么

对象层级	常用命令	作用范围
PostgreSQL 集群	`CREATE ROLE`、`CREATE TABLESPACE`	整个实例
数据库	`CREATE DATABASE`	创建独立数据库
数据库内部	`CREATE SCHEMA`、`CREATE EXTENSION`	当前数据库
Schema 内部	`CREATE TABLE`、`VIEW`、`TYPE`、`FUNCTION`	指定 Schema
表的附属对象	`CREATE INDEX`、`TRIGGER`、`POLICY`	依附于表

对象层级可以理解为：

PostgreSQL 实例
  └── ashare 数据库
       ├── market Schema
       │    ├── security 表
       │    ├── daily_quote 表
       │    ├── latest_quote 视图
       │    └── calc_change_pct() 函数
       └── public Schema

二、CREATE 的通用规则

1. 多数 CREATE 支持事务

BEGIN;

CREATE SCHEMA market;

CREATE TABLE market.test (
    id bigint PRIMARY KEY
);

CREATE INDEX idx_test_id
ON market.test (id);

COMMIT;

中间任何语句失败，都可以整体 ROLLBACK。

但下面几个常见命令不能放进事务块：

CREATE DATABASE
CREATE TABLESPACE
CREATE INDEX CONCURRENTLY

2. `IF NOT EXISTS` 只是不报错

CREATE TABLE IF NOT EXISTS market.security (...);

如果同名表已经存在，PostgreSQL只会跳过创建，不会检查已有表结构是否正确。

例如已有表缺少字段、约束或索引，以上语句仍会成功。因此正式环境不能把 IF NOT EXISTS 当作完整的数据库迁移方案。

3. 并非所有对象都支持 `OR REPLACE`

常见支持对象：

CREATE OR REPLACE VIEW ...
CREATE OR REPLACE FUNCTION ...
CREATE OR REPLACE PROCEDURE ...

表和索引通常不支持：

-- 错误
CREATE OR REPLACE TABLE ...

-- 应使用
ALTER TABLE ...

4. 推荐命名规则

market.daily_quote

建议：

全部使用小写。
使用下划线分隔单词。
显式写 Schema。
不使用空格、中文对象名、大小写混合名称。
约束和索引使用可识别的名称。

未加双引号的名称会自动转换为小写：

CREATE TABLE DailyQuote (...);

实际对象名是：

dailyquote

如果写成：

CREATE TABLE "DailyQuote" (...);

以后每次都必须使用双引号，不推荐。

三、CREATE ROLE：创建角色

PostgreSQL 中“用户”和“角色”本质上是同一种对象，有 LOGIN 权限的角色才可以登录。

CREATE ROLE ashare_owner NOLOGIN;

CREATE ROLE ashare_app
LOGIN
CONNECTION LIMIT 30;

CREATE USER 基本等价于：

CREATE ROLE role_name LOGIN;

推荐分离：

角色	用途
`ashare_owner`	持有数据库对象，不允许登录
`ashare_migrator`	执行数据库迁移
`ashare_app`	应用程序读写
`ashare_reader`	只读查询

不要在迁移脚本中明文保存密码，可以在 psql 中设置：

\password ashare_app

不要给普通应用以下高权限：

SUPERUSER
CREATEDB
CREATEROLE
REPLICATION
BYPASSRLS

角色是整个 PostgreSQL 集群共享的对象。CREATE ROLE 官方文档

四、CREATE DATABASE：创建数据库

基本语法：

CREATE DATABASE database_name
WITH
    OWNER = owner_name
    TEMPLATE = template_name
    ENCODING = 'UTF8';

创建 A 股数据库：

CREATE ROLE ashare_owner NOLOGIN;

CREATE DATABASE ashare
WITH
    OWNER = ashare_owner
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LOCALE_PROVIDER = builtin
    BUILTIN_LOCALE = 'C.UTF-8';

连接数据库：

\connect ashare

重要参数：

参数	说明
`OWNER`	数据库所有者
`TEMPLATE`	从哪个模板数据库复制
`ENCODING`	数据库编码，通常使用 UTF8
`LOCALE_PROVIDER`	`builtin`、`icu` 或 `libc`
`ICU_LOCALE`	ICU 排序区域
`TABLESPACE`	默认表空间
`CONNECTION LIMIT`	数据库连接数限制
`ALLOW_CONNECTIONS`	是否允许连接

注意：

CREATE DATABASE 不能在事务块中执行。
PostgreSQL 18 的 CREATE DATABASE 不支持 IF NOT EXISTS。
编码和默认排序规则创建后不容易更改。
C.UTF-8 适合稳定、快速的字符串排序，但中文名称并不是拼音排序；需要中文语言排序时应测试 ICU。
PostgreSQL 不支持直接跨数据库查询，业务模块通常优先使用不同 Schema 隔离。

CREATE DATABASE 官方文档

五、CREATE SCHEMA：创建命名空间

Schema 类似数据库内部的文件夹，但它不是物理目录。

CREATE SCHEMA IF NOT EXISTS market
AUTHORIZATION ashare_owner;

创建后使用完整名称：

market.security
market.daily_quote
market.trade_calendar

查看当前 Schema：

SELECT current_schema;
SHOW search_path;

设置当前会话搜索路径：

SET search_path = market, pg_catalog;

推荐在程序、迁移和运维 SQL 中使用完整名称：

SELECT *
FROM market.daily_quote;

而不是依赖不明确的 search_path：

SELECT *
FROM daily_quote;

需要注意：Schema 所有者不一定拥有 Schema 内所有对象，表通常由执行 CREATE TABLE 的角色持有。CREATE SCHEMA 官方文档

六、CREATE TABLE：创建表

这是最重要的 CREATE 命令。

1. 基本语法

CREATE TABLE [IF NOT EXISTS] schema_name.table_name (
    column_name data_type column_constraint,
    ...
    table_constraint
);

2. 创建证券基本信息表

CREATE TABLE market.security (
    code        varchar(6) NOT NULL,
    name        text NOT NULL,
    board       text NOT NULL,
    status      text NOT NULL DEFAULT 'listed',
    created_at  timestamptz NOT NULL DEFAULT current_timestamp,

    CONSTRAINT pk_security
        PRIMARY KEY (code),

    CONSTRAINT ck_security_code
        CHECK (code ~ '^[0-9]{6}$'),

    CONSTRAINT ck_security_board
        CHECK (board IN ('main', 'gem', 'star')),

    CONSTRAINT ck_security_status
        CHECK (status IN ('listed', 'suspended', 'delisted'))
);

对于股票代码，推荐：

varchar(6)

或者：

text CHECK (code ~ '^[0-9]{6}$')

不太推荐 char(6)，因为它具有空格填充语义，字符串处理时容易产生困惑。

3. 创建日行情表

CREATE TABLE market.daily_quote (
    code            varchar(6) NOT NULL,
    trade_date      date NOT NULL,

    open_price      numeric(12, 4) NOT NULL,
    high_price      numeric(12, 4) NOT NULL,
    low_price       numeric(12, 4) NOT NULL,
    close_price     numeric(12, 4) NOT NULL,
    previous_close  numeric(12, 4),

    volume          bigint NOT NULL DEFAULT 0,
    amount          numeric(20, 2) NOT NULL DEFAULT 0,

    pct_change      numeric(10, 4)
        GENERATED ALWAYS AS (
            round(
                (close_price - previous_close)
                * 100
                / nullif(previous_close, 0),
                4
            )
        ) STORED,

    source          text NOT NULL DEFAULT 'unknown',
    created_at      timestamptz NOT NULL DEFAULT current_timestamp,
    updated_at      timestamptz NOT NULL DEFAULT current_timestamp,

    CONSTRAINT pk_daily_quote
        PRIMARY KEY (code, trade_date),

    CONSTRAINT fk_daily_quote_security
        FOREIGN KEY (code)
        REFERENCES market.security (code)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,

    CONSTRAINT ck_daily_quote_price
        CHECK (
            low_price > 0
            AND high_price >= low_price
            AND open_price BETWEEN low_price AND high_price
            AND close_price BETWEEN low_price AND high_price
        ),

    CONSTRAINT ck_daily_quote_previous_close
        CHECK (
            previous_close IS NULL
            OR previous_close > 0
        ),

    CONSTRAINT ck_daily_quote_volume
        CHECK (
            volume >= 0
            AND amount >= 0
        )
);

这里使用 (code, trade_date) 作为复合主键，可从数据库层面阻止同一股票、同一交易日产生重复行情。

4. 常用列约束

约束	作用
`NOT NULL`	禁止空值
`DEFAULT`	没有提供字段值时使用默认值
`CHECK`	检查业务条件
`UNIQUE`	保证唯一
`PRIMARY KEY`	主键，唯一且非空
`REFERENCES`	外键
`GENERATED`	生成列
`IDENTITY`	自动编号

DEFAULT

status text DEFAULT 'listed'

默认值只在字段被省略或明确使用 DEFAULT 时生效：

INSERT INTO market.security (code, name, board)
VALUES ('000001', '平安银行', 'main');

显式传入 NULL 不会使用默认值：

INSERT INTO market.security (
    code, name, board, status
)
VALUES (
    '000001', '平安银行', 'main', NULL
);

如果列有 NOT NULL，这条语句将报错。

CHECK

CHECK (volume >= 0)

需要注意：CHECK 表达式结果为 NULL 时也会通过。因此需要禁止空值时，要同时写 NOT NULL。

PRIMARY KEY

PRIMARY KEY (code, trade_date)

它同时提供：

非空约束；
唯一约束；
唯一 B-tree 索引；
被其他表外键引用的目标。

每张表只能有一个主键，但主键可以包含多列。

UNIQUE

UNIQUE (source, source_code)

默认情况下，多个 NULL 不被认为重复。需要把 NULL 也视为相同时，可以使用：

UNIQUE NULLS NOT DISTINCT (source, source_code)

FOREIGN KEY

FOREIGN KEY (code)
REFERENCES market.security (code)
ON DELETE RESTRICT

常用引用动作：

动作	说明
`NO ACTION`	默认，约束检查时不允许破坏引用
`RESTRICT`	立即阻止删除或修改
`CASCADE`	自动修改或删除子表数据
`SET NULL`	子表外键设为 NULL
`SET DEFAULT`	子表外键设为默认值

历史行情通常不适合 ON DELETE CASCADE，否则删除证券资料可能连带删除全部历史数据。

PostgreSQL不会自动为外键的“引用方字段”建立索引，需要根据查询和删除父记录的场景自行创建。上例的主键 (code, trade_date) 已经以 code 开头，可以支持相关检查。

CREATE TABLE 官方文档

七、IDENTITY：自动编号

推荐使用 SQL 标准的 Identity，而不是旧式 serial：

CREATE TABLE market.import_batch (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    source      text NOT NULL,
    imported_at timestamptz NOT NULL DEFAULT current_timestamp,

    CONSTRAINT pk_import_batch PRIMARY KEY (id)
);

两种方式：

GENERATED ALWAYS AS IDENTITY
GENERATED BY DEFAULT AS IDENTITY

区别：

ALWAYS：通常不允许手动指定编号。
BY DEFAULT：允许手动指定编号。
Identity 只负责生成编号，并不自动保证唯一，因此通常还要加 PRIMARY KEY 或 UNIQUE。

序列值可能因为事务回滚、缓存、冲突插入而产生空洞，不能用于要求“连续无缺号”的业务编号。

八、生成列

PostgreSQL 18 支持：

GENERATED ALWAYS AS (expression) STORED
GENERATED ALWAYS AS (expression) VIRTUAL

区别：

类型	计算时间	是否占用存储
`STORED`	写入时计算	是
`VIRTUAL`	查询时计算	否

PostgreSQL 18 如果省略类型，默认是 VIRTUAL：

pct_change numeric
GENERATED ALWAYS AS (...) VIRTUAL

为了与扩展、备份工具和旧版本兼容，生产设计中可显式写出 STORED 或 VIRTUAL。

生成列限制包括：

不能直接写入；
表达式应使用不可变函数；
不能引用另一生成列；
不能使用子查询；
不适合使用当前时间、随机数等会变化的数据。

九、临时表和 UNLOGGED 表

1. 临时表

CREATE TEMP TABLE temp_daily_quote (
    code       varchar(6),
    trade_date date,
    close_price numeric(12, 4)
) ON COMMIT DROP;

ON COMMIT 选项：

选项	效果
`PRESERVE ROWS`	提交后保留数据，默认
`DELETE ROWS`	提交时清空数据
`DROP`	提交时删除临时表

临时表只对当前会话可见。Autovacuum 无法处理临时表，装载大量数据后可以手动执行：

ANALYZE temp_daily_quote;

2. UNLOGGED 表

CREATE UNLOGGED TABLE market.daily_quote_stage (
    LIKE market.daily_quote
    INCLUDING DEFAULTS
    INCLUDING GENERATED
    INCLUDING CONSTRAINTS
);

特点：

大部分数据变更不写 WAL；
导入通常更快；
崩溃或非正常关机后会被清空；
不复制到物理备用服务器。

适合：

可重新生成的中间数据；
ETL 暂存表；
缓存数据。

不适合正式行情、订单、账户等核心数据。

十、复制表结构和查询结果

1. CREATE TABLE LIKE

CREATE TABLE market.daily_quote_backup (
    LIKE market.daily_quote INCLUDING ALL
);

复制表结构，但不复制数据。

INCLUDING ALL 可以复制：

默认值；
CHECK 约束；
Identity；
生成列；
索引、主键和唯一约束；
注释；
存储参数；
扩展统计信息。

注意：外键不会通过 LIKE 完整复制，需要单独检查并创建。

复制数据：

INSERT INTO market.daily_quote_backup
SELECT *
FROM market.daily_quote;

2. CREATE TABLE AS

CREATE TABLE market.daily_quote_2026_snapshot AS
SELECT *
FROM market.daily_quote
WHERE trade_date < DATE '2027-01-01';

它会根据查询结果创建表并写入数据，但一般不会复制原表的：

主键；
唯一约束；
外键；
默认值；
索引；
注释。

只创建结构，不写入数据：

CREATE TABLE market.daily_quote_empty AS
SELECT *
FROM market.daily_quote
WITH NO DATA;

3. 临时查询结果表

CREATE TEMP TABLE latest_quote_temp
ON COMMIT DROP
AS
SELECT DISTINCT ON (code)
       code,
       trade_date,
       close_price
FROM market.daily_quote
ORDER BY code, trade_date DESC;

十一、创建分区表

原生 PostgreSQL 支持：

RANGE：按日期、数值范围；
LIST：按地区、状态、类别；
HASH：按哈希分散数据。

1. 按月份范围分区

CREATE TABLE market.daily_quote_partitioned (
    LIKE market.daily_quote INCLUDING ALL
)
PARTITION BY RANGE (trade_date);

创建 2026 年 9 月分区：

CREATE TABLE market.daily_quote_2026_09
PARTITION OF market.daily_quote_partitioned
FOR VALUES FROM (DATE '2026-09-01')
         TO   (DATE '2026-10-01');

分区区间是：

[2026-09-01, 2026-10-01)

即下限包含，上限不包含。

关键规则：

插入时找不到匹配分区会报错。
分区表的唯一约束、主键通常必须包含全部分区键。
应在父表上创建索引，让 PostgreSQL为各分区建立匹配索引。
创建或删除分区可能锁住父表，生产环境要安排窗口。
不要创建过多过小分区。

2. TimescaleDB 超表

你的 TimescaleDB 环境可以先创建普通表，然后转换为超表：

CREATE EXTENSION IF NOT EXISTS timescaledb;

SELECT create_hypertable(
    'market.daily_quote',
    by_range('trade_date', INTERVAL '1 month'),
    if_not_exists => TRUE
);

如果表中已有数据：

SELECT create_hypertable(
    'market.daily_quote',
    by_range('trade_date', INTERVAL '1 month'),
    if_not_exists => TRUE,
    migrate_data => TRUE
);

注意：

migrate_data => TRUE 可能长时间锁表。
所有唯一索引和主键必须包含时间分区字段。
上例 (code, trade_date) 符合要求。
同一张表不要同时设计成原生分区表和 TimescaleDB 超表。
大表迁移前必须在恢复副本或测试环境演练。

TimescaleDB create_hypertable 文档

十二、CREATE INDEX：创建索引

基本语法：

CREATE [UNIQUE] INDEX [CONCURRENTLY] index_name
ON table_name [USING index_type] (column_or_expression)
[INCLUDE (column)]
[WHERE condition];

1. 常见索引类型

类型	适合场景
B-tree	等值、范围、排序，默认选择
Hash	只支持等值查询
GIN	JSONB、数组、全文检索、`pg_trgm`
GiST	范围、空间、相似度、排斥约束
SP-GiST	前缀、树形、空间分割数据
BRIN	超大且物理顺序相关的时间序列数据

2. 组合索引

CREATE INDEX idx_daily_quote_code_date_desc
ON market.daily_quote (
    code ASC,
    trade_date DESC
);

适合：

SELECT *
FROM market.daily_quote
ORDER BY code, trade_date DESC;

主键 (code, trade_date) 已经可以支持：

WHERE code = '000001'
ORDER BY trade_date DESC;

但对于 code ASC, trade_date DESC 这种混合排序，显式的混合排序索引可能更有效。

3. 覆盖索引

CREATE INDEX idx_daily_quote_date_code
ON market.daily_quote (trade_date, code)
INCLUDE (close_price, volume, amount);

INCLUDE 字段：

不参与索引定位；
不参与唯一判断；
可以帮助产生 Index Only Scan；
会增加索引体积和写入成本。

4. 部分索引

CREATE INDEX idx_security_listed_board
ON market.security (board, code)
WHERE status = 'listed';

适合经常只查询上市股票：

SELECT code, name
FROM market.security
WHERE status = 'listed'
  AND board = 'main';

查询条件必须能够推导出部分索引的 WHERE 条件，优化器才能使用它。

5. 表达式索引

CREATE INDEX idx_security_code_prefix
ON market.security ((left(code, 2)));

支持：

SELECT *
FROM market.security
WHERE left(code, 2) = '60';

表达式索引使用的函数必须是 IMMUTABLE。

6. BRIN 索引

如果行情数据在物理上基本按交易日期写入，可考虑：

CREATE INDEX idx_daily_quote_trade_date_brin
ON market.daily_quote
USING brin (trade_date)
WITH (pages_per_range = 64);

BRIN 很小，适合超大时间序列表，但过滤精度低于 B-tree。应通过 EXPLAIN (ANALYZE, BUFFERS) 验证，而不是同时盲目创建多种索引。

7. 在线创建索引

普通创建：

CREATE INDEX idx_name
ON market.large_table (column_name);

会阻塞该表的写操作。

生产环境普通表可以考虑：

CREATE INDEX CONCURRENTLY idx_name
ON market.large_table (column_name);

但需要注意：

不能放在事务块中；
执行时间和资源消耗更大；
同一张表同时只能进行一个并发索引构建；
失败可能留下无效索引；
原生分区父表不支持直接并发构建；
TimescaleDB 超表应按扩展对应版本的索引创建方式执行。

检查无效索引：

SELECT
    indexrelid::regclass AS index_name,
    indrelid::regclass AS table_name,
    indisready,
    indisvalid
FROM pg_index
WHERE NOT indisready
   OR NOT indisvalid;

IF NOT EXISTS 同样不会检查已有索引定义是否符合预期。CREATE INDEX 官方文档

十三、CREATE VIEW：创建视图

普通视图保存的是查询定义，不保存查询结果。

CREATE OR REPLACE VIEW market.latest_quote AS
SELECT DISTINCT ON (code)
       code,
       trade_date,
       open_price,
       high_price,
       low_price,
       close_price,
       volume,
       amount
FROM market.daily_quote
ORDER BY code, trade_date DESC;

查询：

SELECT *
FROM market.latest_quote
WHERE code LIKE '60%';

特点：

不额外存储数据；
每次查询时执行底层 SQL；
简化复杂查询；
可以隐藏敏感字段；
底层表慢，视图通常也会慢。

CREATE OR REPLACE VIEW 不能任意删除、重命名或修改已有输出列类型。复杂结构变化通常需要迁移处理。

简单视图可能可更新：

CREATE VIEW market.listed_security AS
SELECT code, name, board, status
FROM market.security
WHERE status = 'listed'
WITH LOCAL CHECK OPTION;

通过该视图写入时，CHECK OPTION 会保证写入后的行仍然满足视图条件。CREATE VIEW 官方文档

十四、CREATE MATERIALIZED VIEW：物化视图

物化视图会保存查询结果：

CREATE MATERIALIZED VIEW market.monthly_quote_summary AS
SELECT
    code,
    date_trunc('month', trade_date)::date AS month_date,
    min(low_price)  AS month_low,
    max(high_price) AS month_high,
    sum(volume)     AS month_volume,
    sum(amount)     AS month_amount
FROM market.daily_quote
GROUP BY
    code,
    date_trunc('month', trade_date)::date
WITH NO DATA;

创建唯一索引：

CREATE UNIQUE INDEX uq_monthly_quote_summary
ON market.monthly_quote_summary (
    code,
    month_date
);

第一次填充：

REFRESH MATERIALIZED VIEW
market.monthly_quote_summary;

以后在线刷新：

REFRESH MATERIALIZED VIEW CONCURRENTLY
market.monthly_quote_summary;

区别：

普通视图	物化视图
不存储结果	存储结果
数据实时	需要刷新
查询可能较慢	查询通常更快
不占结果存储空间	占用存储空间

并发刷新通常需要覆盖所有行的有效唯一索引。TimescaleDB 的时间序列汇总还可以考虑 Continuous Aggregate。物化视图官方文档

十五、CREATE SEQUENCE：创建序列

CREATE SEQUENCE market.import_batch_seq
AS bigint
START WITH 1
INCREMENT BY 1
NO CYCLE
CACHE 20;

获取下一个值：

SELECT nextval('market.import_batch_seq');

查看当前会话最近取得的值：

SELECT currval('market.import_batch_seq');

与字段绑定：

CREATE SEQUENCE market.order_seq
OWNED BY market.import_batch.id;

关键认识：

nextval() 不会因为事务回滚而回退。
Sequence 保证并发唯一分配，不保证连续。
CACHE 越大性能可能越好，但异常退出时空洞可能越多。
普通主键优先使用 Identity，独立业务序列才显式创建 Sequence。

CREATE SEQUENCE 官方文档

十六、CREATE TYPE 与 CREATE DOMAIN

1. 枚举类型

CREATE TYPE market.board_type AS ENUM (
    'main',
    'gem',
    'star'
);

使用：

CREATE TABLE market.security_enum_example (
    code  varchar(6) PRIMARY KEY,
    board market.board_type NOT NULL
);

枚举适合极其稳定的值。枚举值可以追加，但删除、重排或大规模修改不方便。

如果业务分类经常变化，建议使用字典表和外键。

2. Domain

Domain 是“基础类型 + 公共约束”：

CREATE DOMAIN market.stock_code AS varchar(6)
CHECK (VALUE ~ '^[0-9]{6}$');

使用：

CREATE TABLE market.watchlist (
    code       market.stock_code NOT NULL,
    added_at   timestamptz NOT NULL DEFAULT current_timestamp
);

适合多个表重复使用相同规则。

官方建议 Domain 本身通常允许 NULL，是否非空由具体表字段的 NOT NULL 控制。CREATE DOMAIN 文档

3. 复合类型

CREATE TYPE market.price_range AS (
    low_price  numeric(12, 4),
    high_price numeric(12, 4)
);

适合函数参数或返回结果，但普通业务表通常优先使用独立字段。

CREATE TYPE 官方文档

十七、CREATE FUNCTION：创建函数

创建涨跌幅计算函数：

CREATE OR REPLACE FUNCTION market.calc_change_pct(
    p_close numeric,
    p_previous_close numeric
)
RETURNS numeric
LANGUAGE sql
IMMUTABLE
STRICT
PARALLEL SAFE
RETURN round(
    (p_close - p_previous_close)
    * 100
    / nullif(p_previous_close, 0),
    4
);

使用：

SELECT market.calc_change_pct(10.50, 10.00);

常用属性：

属性	含义
`IMMUTABLE`	相同参数永远得到相同结果
`STABLE`	同一条语句内结果稳定
`VOLATILE`	每次调用结果都可能变化，默认
`STRICT`	任意参数为 NULL 时直接返回 NULL
`PARALLEL SAFE`	可以在并行工作进程中执行
`SECURITY INVOKER`	使用调用者权限，默认
`SECURITY DEFINER`	使用函数所有者权限

不要为了让表达式索引创建成功，就把实际会变化的函数错误标记为 IMMUTABLE，这可能产生错误查询结果。

SECURITY DEFINER 风险较高，至少应：

设置安全的 search_path；
使用完整对象名称；
撤销 PUBLIC 的默认执行权限；
只向指定角色授权。

CREATE FUNCTION 官方文档

十八、CREATE PROCEDURE：创建存储过程

函数通常返回结果并参与查询；存储过程通过 CALL 执行，更适合执行一组操作。

CREATE OR REPLACE PROCEDURE market.refresh_statistics()
LANGUAGE plpgsql
AS $$
BEGIN
    ANALYZE market.security;
    ANALYZE market.daily_quote;
END;
$$;

调用：

CALL market.refresh_statistics();

函数	存储过程
`SELECT function()`	`CALL procedure()`
可以返回值或结果集	主要执行操作
可参与 SQL 表达式	不直接参与表达式
一般不能控制外层事务	特定调用条件下可进行事务控制

CREATE PROCEDURE 官方文档

十九、CREATE TRIGGER：创建触发器

updated_at DEFAULT current_timestamp 只在插入时执行，更新行时不会自动变化。

可以创建触发器：

CREATE OR REPLACE FUNCTION market.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := clock_timestamp();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_daily_quote_set_updated_at
BEFORE UPDATE
ON market.daily_quote
FOR EACH ROW
EXECUTE FUNCTION market.set_updated_at();

触发器分类：

类型	执行时间
`BEFORE`	修改数据前
`AFTER`	修改数据后
`INSTEAD OF`	替代视图上的原操作
`FOR EACH ROW`	每行执行一次
`FOR EACH STATEMENT`	每条语句执行一次

触发器适合：

更新时间；
审计日志；
数据同步；
强制数据库层规则。

但触发器会隐藏执行逻辑，批量导入时还可能显著增加开销，不要把所有业务逻辑都塞进触发器。CREATE TRIGGER 官方文档

二十、CREATE EXTENSION：安装扩展

CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gist;

查看已安装扩展：

SELECT
    extname,
    extversion
FROM pg_extension
ORDER BY extname;

查看服务器可用扩展：

SELECT
    name,
    default_version,
    installed_version
FROM pg_available_extensions
ORDER BY name;

注意：

扩展软件包必须先安装在服务器上。
CREATE EXTENSION 是在当前数据库中注册并创建扩展对象。
每个数据库通常需要分别执行。
IF NOT EXISTS 不会自动升级旧版本。
更新扩展使用 ALTER EXTENSION ... UPDATE。
只安装可信扩展，并放在不允许普通用户创建对象的安全 Schema 中。

CREATE EXTENSION 官方文档

二十一、CREATE POLICY：行级安全策略

适合一个表存储多个用户或机构的数据。

假设表中有 owner_name：

ALTER TABLE market.watchlist
ENABLE ROW LEVEL SECURITY;

CREATE POLICY watchlist_owner_policy
ON market.watchlist
FOR ALL
TO ashare_app
USING (
    owner_name = current_user
)
WITH CHECK (
    owner_name = current_user
);

区别：

USING：哪些已有行可以查询、修改或删除。
WITH CHECK：哪些新行或修改后的行允许写入。

只执行 CREATE POLICY 不会自动启用 RLS，必须执行：

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

启用 RLS 但没有适用策略时，默认拒绝访问。表所有者和超级用户通常可能绕过 RLS，需要时可使用 FORCE ROW LEVEL SECURITY。CREATE POLICY 官方文档

二十二、CREATE STATISTICS：扩展统计信息

索引解决访问问题，扩展统计信息帮助优化器更准确地估算多列关系。

例如 board 和 status 可能高度相关：

CREATE STATISTICS st_security_board_status (
    dependencies,
    mcv
)
ON board, status
FROM market.security;

然后执行：

ANALYZE market.security;

适合：

多列强相关；
多列组合值分布不均匀；
执行计划行数估算长期偏差。

它不存储查询结果，也不能替代索引。

二十三、其他 CREATE 命令

命令	用途
`CREATE TABLESPACE`	把表或索引放到其他存储位置
`CREATE COLLATION`	创建排序规则
`CREATE PUBLICATION`	逻辑复制发布端
`CREATE SUBSCRIPTION`	逻辑复制订阅端
`CREATE FOREIGN TABLE`	映射外部数据源
`CREATE SERVER`	定义外部服务器
`CREATE USER MAPPING`	外部服务器用户映射
`CREATE AGGREGATE`	自定义聚合函数
`CREATE OPERATOR`	自定义操作符
`CREATE EVENT TRIGGER`	监听 DDL 事件

CREATE TABLESPACE 属于集群级高风险运维操作：

CREATE TABLESPACE fast_space
OWNER ashare_owner
LOCATION '/data/postgresql/fast_space';

要求：

目录预先存在；
目录为空；
由 PostgreSQL 系统用户拥有；
使用绝对路径；
不能在事务块内执行；
不能把表空间目录当作独立备份。

二十四、创建后的检查

psql 常用命令：

\l
\du
\dn
\dt market.*
\d+ market.daily_quote
\di+ market.*
\dv+ market.*
\dm+ market.*
\df+ market.*
\dx

查看视图定义：

SELECT pg_get_viewdef(
    'market.latest_quote'::regclass,
    true
);

查看表的索引定义：

SELECT pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indrelid = 'market.daily_quote'::regclass;

导出建表结构：

pg_dump \
  --dbname=ashare \
  --schema-only \
  --table=market.daily_quote

添加注释：

COMMENT ON TABLE market.daily_quote
IS 'A股日线行情，每只证券每个交易日一条记录';

COMMENT ON COLUMN market.daily_quote.amount
IS '成交金额，单位由数据源规范统一';

二十五、CREATE 常见错误

把 CRUD 的 Create 和 SQL 的 CREATE 混为一谈。
用 IF NOT EXISTS 代替版本化迁移。
不指定 Schema，导致对象创建到错误位置。
使用 "DailyQuote" 这类大小写敏感名称。
股票代码用整数，导致 000001 变成 1。
使用 char(n) 后忽略空格填充语义。
表没有主键或业务唯一约束。
认为 DEFAULT updated_at 会在更新时自动变化。
外键引用列没有合适索引。
一张表创建大量重复索引。
在生产大表上直接执行普通 CREATE INDEX。
把正式数据放入 UNLOGGED 表。
认为 Sequence 一定连续。
错误标记函数为 IMMUTABLE。
应用账户同时作为对象所有者和超级用户。
创建物化视图后忘记刷新。
TimescaleDB 唯一索引没有包含时间分区列。
用 CREATE TABLE AS 后误以为主键、约束和索引也被复制。

SQLAlchemy 2.0 特别注意

Base.metadata.create_all(engine)

主要用于创建当前不存在的表，并不会可靠完成：

修改已有字段类型；
删除旧字段；
添加或调整复杂约束；
重建索引；
管理生产数据库版本。

正式项目应使用 Alembic 等迁移工具管理 DDL 变化。