CREATE TYPE 自定义类型

PostgreSQL 允许你用 CREATE TYPE 定义自己的数据类型。它不只支持"复合类型（Composite Type）"这一种形态，还可以创建枚举（Enum）、范围（Range）乃至底层的基类型（Base Type）。自定义类型是把业务概念固化进数据库 schema 的关键手段，能让约束、函数签名、表结构都表达得更准确。

前置知识

阅读本节前，建议先了解：PostGIS 入门

基础概念

为什么需要自定义类型

系统内置类型覆盖的是"通用数据形状"，而业务里经常出现"领域概念"：

业务概念	用内置类型的困境	用自定义类型的好处
订单状态	用 `text` 存，只能靠 `CHECK` 约束，值域散落在各处	枚举类型统一值域，编译期（写入时）就报错
收货地址	拆成 5 列，函数签名要传 5 个参数	复合类型打包，函数只传 1 个参数
价格区间	用 `min_price` + `max_price` 两列，容易忘校验 `min <= max`	范围类型自带合法性校验
手机号 / 邮箱	每个表都写一遍 `CHECK`，改规则要改 N 张表	域类型一处定义、处处复用

一句话：自定义类型把"这条数据长什么样"从应用层下沉到数据库层，让约束更早生效、让 schema 自解释。

CREATE TYPE 的四种形态

CREATE TYPE 一个命令覆盖四种完全不同的东西：

形态	语法关键词	本质	可否在表中作列类型	典型用途
复合类型	`AS (field type, ...)`	多个字段打包成一个值	可以	地址、坐标、金额结构
枚举类型	`AS ENUM ('a', 'b')`	有限固定值集合	可以	状态机、类型标签
范围类型	`AS RANGE (SUBTYPE = ...)`	连续区间	可以	价格区间、时间段
基类型	`(INPUT = ..., OUTPUT = ...)`	全新底层类型	可以	扩展开发、复杂数值

sql

-- 四种形态的最小示例（仅示意语法差异）
CREATE TYPE addr AS (city text, street text);                          -- 复合
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');                       -- 枚举
CREATE TYPE floatrange AS RANGE (SUBTYPE = float8);                    -- 范围
CREATE TYPE int42 (INPUT = int42_in, OUTPUT = int42_out);              -- 基类型

术语提示

复合类型（Composite Type）、枚举类型（Enum Type）、范围类型（Range Type）、基类型（Base Type）、外壳类型（Shell Type）、类型依赖（Type Dependency）、系统目录（System Catalog）、行类型（Row Type）。

自定义类型存在哪里

所有类型都记录在系统目录 pg_type 中，与内置类型一视同仁。

sql

-- 查看当前库的全部自定义类型（排除数组类型与系统类型）
SELECT typname, typtype, typcategory
FROM pg_type
WHERE typnamespace = 'public'::regnamespace
ORDER BY typname;

text

   typname    | typtype | typcategory
--------------+---------+-------------
 addr         | c       | C
 mood         | e       | E
 order_status | e       | E
 floatrange   | r       | R
(4 rows)

typtype 的取值含义：

typtype	英文	含义
`b`	Base	基类型（含所有系统内置标量类型）
`c`	Composite	复合类型
`d`	Domain	域
`e`	Enum	枚举类型
`p`	Pseudo	伪类型（如 `anyelement`、`void`）
`r`	Range	范围类型
`m`	Multirange	多范围类型（PostgreSQL 14+）

sql

-- 用 \dT 元命令直观查看（+ 显示更多细节）
\dT+

text

                                        List of data types
 Schema |     Name     | Internal name | Size  | Elements |  Owner   | Access privileges | Description
--------+--------------+---------------+-------+----------+----------+-------------------+-------------
 public | addr         | addr          | tuple |          | postgres |                   |
 public | floatrange   | floatrange    | var   |          | postgres |                   |
 public | mood         | mood          | 4     | sad     +| postgres |                   |
        |              |               |       | ok      +|          |                   |
        |              |               |       | happy    |          |                   |
 public | order_status | order_status  | 4     | pending +| postgres |                   |
        |              |               |       | paid    +|          |                   |
        |              |               |       | shipped +|          |                   |
        |              |               |       | done     |          |                   |
(4 rows)

类型的命名空间与可见性

类型和表、函数一样属于 schema，遵循 search_path 解析规则。

sql

-- 建议为业务类型单独建 schema，避免污染 public
CREATE SCHEMA types;

CREATE TYPE types.money_cny AS (amount numeric(12,2), currency char(3));

-- 使用时要带 schema 前缀，或把 types 加进 search_path
SET search_path = types, public;

SELECT ROW(12.50, 'CNY')::money_cny AS price;

text

        price
---------------------
 (12.50,CNY)
(1 row)

语法与用法

复合类型

最常用的一种。把若干字段打包成一个值。

sql

CREATE TYPE addr AS (
  province text,
  city     text,
  street   text,
  zipcode  char(6)
);

-- 作为列类型使用
CREATE TABLE customers (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    text   NOT NULL,
  address addr
);

INSERT INTO customers (name, address)
VALUES ('张三', ROW('浙江省', '杭州市', '西湖区文三路 100 号', '310012'));

sql

SELECT * FROM customers;

text

 id | name |                        address
----+------+--------------------------------------------------------
  1 | 张三 | (浙江省,杭州市,"西湖区文三路 100 号",310012)
(1 row)

枚举类型

sql

CREATE TYPE order_status AS ENUM (
  'pending',    -- 待付款
  'paid',       -- 已付款
  'shipped',    -- 已发货
  'done',       -- 已完成
  'cancelled'   -- 已取消
);

CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'pending'
);

sql

-- 合法值：写入成功
INSERT INTO orders (status) VALUES ('paid'), ('shipped');

-- 错误！不在枚举值域内
INSERT INTO orders (status) VALUES ('refunded');

text

ERROR:  invalid input value for enum order_status: "refunded"
LINE 1: INSERT INTO orders (status) VALUES ('refunded');
                                            ^

范围类型

sql

-- 自定义一个整数区间类型（内置已有 int4range，这里演示语法）
CREATE TYPE int_span AS RANGE (
  SUBTYPE = integer,
  SUBTYPE_DIFF = int4mi,       -- 加速宽度的计算，可选
  MULTIRANGE_TYPE_NAME = int_span_multirange
);

SELECT '[1,10)'::int_span AS span, upper('[1,10)'::int_span) AS hi;

text

   span   | hi
----------+----
 [1,10)   | 10
(1 row)

版本说明

MULTIRANGE_TYPE_NAME 参数从 PostgreSQL 14 起支持，用于指定自动生成的多范围类型名。

基类型（Shell Type）

基类型是最底层的形态，需要用 C 语言实现 INPUT / OUTPUT 函数。真正的开发流程分两步，先用"外壳类型"占位：

sql

-- 第一步：创建外壳类型（只有名字，没有定义）
CREATE TYPE complex_num;

-- 第二步：创建输入输出函数（C 语言，这里仅示意）
-- CREATE FUNCTION complex_in(cstring) RETURNS complex_num
--   AS 'filename' LANGUAGE C IMMUTABLE STRICT;
-- CREATE FUNCTION complex_out(complex_num) RETURNS cstring
--   AS 'filename' LANGUAGE C IMMUTABLE STRICT;

-- 第三步：补齐类型定义
-- CREATE TYPE complex_num (
--   INPUT = complex_in,
--   OUTPUT = complex_out,
--   INTERNALLENGTH = 16,
--   ALIGNMENT = double,
--   STORAGE = plain,
--   PASSEDBYVALUE
-- );

外壳类型是"半成品"

只 CREATE TYPE xxx; 而不提供 INPUT / OUTPUT 函数，这个类型无法用于任何实际运算（连 SELECT 都会报错）。只有在开发 C 扩展的过程中才会短暂处于这个状态。

域（Domain）

域是"带约束的基础类型"，语法主线是 CREATE DOMAIN，通常与 CREATE TYPE 一起讲解。

sql

CREATE DOMAIN email AS text
  CHECK (VALUE ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN positive_int AS integer
  CHECK (VALUE > 0);

SELECT 'a@b.com'::email AS ok;

text

    ok
-----------
 a@b.com
(1 row)

sql

-- 错误！不符合域约束
SELECT 'not-an-email'::email;

text

ERROR:  value for domain email violates check constraint "email_check"

管理命令总表

命令	作用	说明
`CREATE TYPE`	创建类型	四种形态共用一个命令
`CREATE DOMAIN`	创建域	域用独立命令，不属于 `CREATE TYPE`
`ALTER TYPE ... ADD VALUE`	枚举加值	最常用的变更
`ALTER TYPE ... RENAME VALUE`	枚举改值名	PostgreSQL 10+
`ALTER TYPE ... RENAME TO`	类型改名	需重建依赖对象时才需要
`ALTER TYPE ... ADD ATTRIBUTE`	复合类型加字段	需重建依赖视图/函数
`ALTER TYPE ... OWNER TO`	改属主	权限管理
`ALTER TYPE ... SET SCHEMA`	移到别的 schema	需相应调整 `search_path`
`DROP TYPE`	删除类型	有依赖时需 `CASCADE` 或先解依赖
`DROP TYPE ... CASCADE`	级联删除	危险，会连带删掉使用该类型的列
`COMMENT ON TYPE`	加注释	强烈建议对业务类型写注释

查看类型定义的三种方式

sql

-- 1. 元命令（最方便）
\dT+ order_status

text

                                      List of data types
 Schema |     Name     | Internal name | Size |   Elements   |  Owner   | Access privileges | Description
--------+--------------+---------------+------+--------------+----------+-------------------+-------------
 public | order_status | order_status  | 4    | pending     +| postgres |                   |
        |              |               |      | paid        +|          |                   |
        |              |               |      | shipped     +|          |                   |
        |              |               |      | done        +|          |                   |
        |              |               |      | cancelled    |          |                   |
(1 row)

sql

-- 2. 查 pg_enum 看枚举值与排序号
SELECT enumtypid::regtype AS type,
       enumlabel        AS label,
       enumsortorder    AS ord
FROM pg_enum
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;

text

     type     |  label   | ord
--------------+----------+-----
 order_status | pending  |   1
 order_status | paid     |   2
 order_status | shipped  |   3
 order_status | done     |   4
 order_status | cancelled|   5
(5 rows)

sql

-- 3. 列出所有依赖该类型的对象（改类型前必须看）
SELECT c.relname AS table_name,
       a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
WHERE a.atttypid = 'order_status'::regtype
  AND a.attnum > 0
  AND NOT a.attisdropped;

text

 table_name | column_name
------------+-------------
 orders     | status
(1 row)

详细说明

复合类型的字段访问与构造

复合类型的值可以用 (值).字段 或 (值).* 访问。

sql

-- 访问单个字段
SELECT name,
       (address).city     AS city,
       (address).zipcode  AS zip
FROM customers;

text

 name |  city  |  zip
------+--------+--------
 张三 | 杭州市 | 310012
(1 row)

sql

-- 把复合类型展开成多列（聚合场景很实用）
SELECT name, (address).*
FROM customers;

text

 name | province |  city  |        street         |  zip
------+----------+--------+-----------------------+--------
 张三 | 浙江省   | 杭州市 | 西湖区文三路 100 号   | 310012
(1 row)

sql

-- 复合类型值可以整体比较、赋值
UPDATE customers
SET address = ROW('浙江省', '杭州市', '滨江区江南大道 1 号', '310052')
WHERE id = 1;

复合类型 vs 拆列 vs JSONB

对比项	拆成多列	复合类型	JSONB
字段类型校验	强（逐列定义）	强（逐字段定义）	弱（全靠约定）
字段增删	`ALTER TABLE`（PG11+ 加列快）	需重建依赖对象	无成本
单字段索引	直接建	需表达式索引	需表达式索引
整体传参	需传多个参数	传一个值	传一个值
查询可读性	最好	中等（要写 `(x).f`）	差（`->>` 链）
适合场景	常用查询的独立维度	作为函数参数/返回值	半结构化、schema 多变

结论：表中的数据优先拆列，复合类型主要用于函数的参数与返回值。

枚举的内部存储与排序

枚举底层存的是 enumsortorder（浮点数），显示时再映射成标签。

sql

-- 枚举排序按"声明顺序"，不是字母序
SELECT * FROM (VALUES ('done'::order_status), ('pending'::order_status), ('paid'::order_status)) AS t(s)
ORDER BY s;

text

   s
---------
 pending
 paid
 done
(3 rows)

sql

-- 对比：按文本排序结果是字母序，完全不同
SELECT * FROM (VALUES ('done'), ('pending'), ('paid')) AS t(s) ORDER BY s;

text

   s
---------
 done
 paid
 pending
(3 rows)

sql

-- 枚举占 4 字节，值少于 2^32 个时空间效率与 integer 相当
SELECT typname,
       typlen                       AS bytes,
       (SELECT count(*) FROM pg_enum e WHERE e.enumtypid = t.oid) AS n_values
FROM pg_type t
WHERE typname = 'order_status';

text

   typname    | bytes | n_values
--------------+-------+----------
 order_status |     4 |        5
(1 row)

ALTER TYPE 的能力边界

枚举类型一旦创建，能做的变更是受限的：

操作	是否支持	语句	备注
追加新值到末尾	支持	`ALTER TYPE ... ADD VALUE 'new'`	最常用
追加并指定位置	支持	`ALTER TYPE ... ADD VALUE 'new' BEFORE 'paid'`	需要重写表，开销大
重命名某个值	支持	`ALTER TYPE ... RENAME VALUE 'old' TO 'new'`	PostgreSQL 10+
删除某个值	不支持	—	必须重建类型
修改值的顺序	不支持	—	只能靠 `ADD VALUE ... BEFORE/AFTER` 新增
重命名类型本身	支持	`ALTER TYPE ... RENAME TO`	依赖对象自动跟随

sql

-- 追加新值（PostgreSQL 12+ 可在事务块中执行）
ALTER TYPE order_status ADD VALUE IF NOT EXISTS 'refunding' AFTER 'paid';

text

ALTER TYPE

sql

-- 验证新值已生效且位置正确
SELECT enumlabel, enumsortorder
FROM pg_enum
WHERE enumtypid = 'order_status'::regtype
ORDER BY enumsortorder;

text

  enumlabel  | enumsortorder
-------------+---------------
 pending     |             1
 paid        |             2
 refunding   |             3
 shipped     |             4
 done        |             5
 cancelled   |             6
(6 rows)

版本说明

PostgreSQL 12 之前，ALTER TYPE ... ADD VALUE 不能在事务块中执行，也不能在同一个事务里立即使用新值。PostgreSQL 12+ 放开了这个限制，但仍不能在同一个事务中"新增值 + 使用该值"（因为新值在事务快照中不可见）。

依赖关系：为什么删不掉

类型被引用后，DROP TYPE 会失败并列出依赖。

sql

DROP TYPE order_status;

text

ERROR:  cannot drop type order_status because other objects depend on it
DETAIL:  column status of table orders depends on type order_status
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

sql

-- 安全做法：先查依赖，再决定怎么处理
SELECT dependent_ns.nspname AS schema,
       dependent_view.relname AS object,
       dependent_view.relkind AS kind
FROM pg_depend d
JOIN pg_class dependent_view ON d.objid = dependent_view.oid
JOIN pg_namespace dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE d.refobjid = 'order_status'::regtype
  AND d.deptype = 'n';

text

 schema | object | kind
--------+--------+------
 public | orders | r
(1 row)

枚举改值域的高成本路径

想"删掉枚举里的某个值"或"调整顺序"，官方只提供一条路：新建类型 → 改列类型 → 删旧类型。

sql

-- 1. 建新类型（去掉 cancelled）
CREATE TYPE order_status_new AS ENUM ('pending', 'paid', 'refunding', 'shipped', 'done');

-- 2. 改列类型（会重写整张表，大表很慢，且需要 ACCESS EXCLUSIVE 锁）
ALTER TABLE orders
  ALTER COLUMN status TYPE order_status_new
  USING status::text::order_status_new;

-- 3. 删旧类型、重命名新类型
DROP TYPE order_status;
ALTER TYPE order_status_new RENAME TO order_status;

因此设计枚举时宁可多留几个"预留值"，也别事后改值域。

复合类型的修改是"重建级"的

给复合类型加字段虽然支持，但会导致所有依赖它的对象失效。

sql

-- 加字段本身是支持的
ALTER TYPE addr ADD ATTRIBUTE country text;

-- 但依赖它的视图/函数可能报错，需要重建
-- ERROR: cannot alter type "addr" because column "customers.address" uses it

text

ERROR:  cannot alter type "addr" because column "customers.address" uses it
HINT:  Use ALTER TABLE ... ALTER COLUMN ... TYPE ... to change the column type.

正确做法：先删依赖列 → 改类型 → 重建列并迁移数据。

sql

-- 1. 先把数据搬到临时列
ALTER TABLE customers ADD COLUMN address_new text;

-- 2. 删掉使用该类型的列
ALTER TABLE customers DROP COLUMN address;

-- 3. 修改类型
ALTER TYPE addr ADD ATTRIBUTE country text;

-- 4. 重建列（历史数据需单独迁移，此处略）
ALTER TABLE customers ADD COLUMN address addr;

实战示例

案例一：订单状态机（枚举类型）

sql

DROP TABLE IF EXISTS orders CASCADE;
DROP TYPE IF EXISTS order_status CASCADE;

CREATE TYPE order_status AS ENUM (
  'pending',    -- 待付款
  'paid',       -- 已付款
  'shipped',    -- 已发货
  'done',       -- 已完成
  'cancelled'   -- 已取消
);

COMMENT ON TYPE order_status IS '订单状态机：pending → paid → shipped → done，任意状态可转 cancelled';

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    char(16)     NOT NULL UNIQUE,
  status      order_status NOT NULL DEFAULT 'pending',
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0),
  created_at  timestamptz  NOT NULL DEFAULT now(),
  updated_at  timestamptz  NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_status_created ON orders (status, created_at DESC);

步骤 1：插入数据

sql

-- 1. 插入一批订单
INSERT INTO orders (order_no, status, amount) VALUES
  ('2026090100000001', 'done',      1280.00),
  ('2026090100000002', 'paid',       390.50),
  ('2026090100000003', 'pending',    899.00),
  ('2026090100000004', 'shipped',   1580.00),
  ('2026090100000005', 'cancelled',  260.00),
  ('2026090100000006', 'paid',       520.00);

-- 2. 不写 status 时用默认值
INSERT INTO orders (order_no, amount) VALUES ('2026090100000007', 99.90)
RETURNING id, order_no, status;

text

 id |      order_no      | status
----+--------------------+---------
  7 | 2026090100000007   | pending
(1 row)

步骤 2：按业务顺序而非字母序统计

sql

-- 3. 按状态聚合，结果顺序跟枚举声明一致
SELECT status, count(*) AS cnt, sum(amount) AS total
FROM orders
GROUP BY status
ORDER BY status;

text

  status   | cnt |  total
-----------+-----+---------
 pending   |   2 |  998.90
 paid      |   2 |  910.50
 shipped   |   1 | 1580.00
 done      |   1 | 1280.00
 cancelled |   1 |  260.00
(5 rows)

步骤 3：写状态流转校验函数

sql

-- 4. 用枚举类型做函数参数，语义清晰
CREATE OR REPLACE FUNCTION transit_order(p_order_id bigint, p_to order_status)
RETURNS order_status
LANGUAGE plpgsql AS $$
DECLARE
  v_from order_status;
  v_ok   boolean;
BEGIN
  SELECT status INTO v_from FROM orders WHERE id = p_order_id FOR UPDATE;

  v_ok := CASE
    WHEN v_from = 'pending'   AND p_to IN ('paid', 'cancelled')       THEN true
    WHEN v_from = 'paid'      AND p_to IN ('shipped', 'cancelled')    THEN true
    WHEN v_from = 'shipped'   AND p_to = 'done'                       THEN true
    ELSE false
  END;

  IF NOT v_ok THEN
    RAISE EXCEPTION '非法状态流转：% -> %', v_from, p_to;
  END IF;

  UPDATE orders
  SET status = p_to, updated_at = now()
  WHERE id = p_order_id;

  RETURN p_to;
END;
$$;

sql

-- 5. 正常流转
SELECT transit_order(3, 'paid') AS new_status;

text

 new_status
------------
 paid
(1 row)

sql

-- 6. 错误！pending 不能直接到 shipped
SELECT transit_order(7, 'shipped');

text

ERROR:  非法状态流转：pending -> shipped
CONTEXT:  PL/pgSQL function transit_order(bigint,order_status) line 17 at RAISE

步骤 4：查状态分布

sql

-- 7. 哪个状态积压最多
SELECT status,
       count(*) AS cnt,
       round(avg(amount), 2) AS avg_amount
FROM orders
GROUP BY status
ORDER BY cnt DESC, status;

text

  status   | cnt | avg_amount
-----------+-----+------------
 pending   |   2 |     548.95
 paid      |   2 |     455.25
 shipped   |   1 |    1580.00
 done      |   1 |    1280.00
 cancelled |   1 |     260.00
(5 rows)

案例二：复合类型作为函数参数与返回值

sql

-- 1. 定义地址复合类型
DROP TYPE IF EXISTS addr CASCADE;

CREATE TYPE addr AS (
  province text,
  city     text,
  district text,
  street   text,
  zipcode  char(6)
);

COMMENT ON TYPE addr IS '中国大陆收货地址，zipcode 为 6 位邮政编码';

SELECT typname, typtype
FROM pg_type
WHERE typname = 'addr' AND typnamespace = 'public'::regnamespace;

text

 typname | typtype
---------+---------
 addr    | c
(1 row)

sql

-- 2. 用复合类型做返回值：格式化地址
CREATE OR REPLACE FUNCTION fmt_addr(a addr, p_sep text DEFAULT '')
RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT concat_ws(p_sep, a.province, a.city, a.district, a.street, '(' || a.zipcode || ')');
$$;

-- 3. 用复合类型做参数：校验邮编与城市是否匹配
CREATE OR REPLACE FUNCTION valid_zip_prefix(a addr, p_prefix char(2))
RETURNS boolean
LANGUAGE sql IMMUTABLE AS $$
  SELECT left(a.zipcode, 2) = p_prefix;
$$;

步骤 1：不带分隔符

sql

-- 4. 默认拼接（无分隔符）
SELECT fmt_addr(ROW('浙江省', '杭州市', '西湖区', '文三路 100 号', '310012')::addr) AS full_addr;

text

                              full_addr
-----------------------------------------------------------------------
 浙江省杭州市西湖区文三路 100 号(310012)
(1 row)

步骤 2：带分隔符

sql

-- 5. 带空格分隔，更可读
SELECT fmt_addr(ROW('浙江省', '杭州市', '西湖区', '文三路 100 号', '310012')::addr, ' ') AS full_addr;

text

                                  full_addr
------------------------------------------------------------------------------
 浙江省 杭州市 西湖区 文三路 100 号 (310012)
(1 row)

步骤 3：校验邮编前缀

sql

-- 6. 31 表示浙江，正确
SELECT valid_zip_prefix(ROW('浙江省', '杭州市', '西湖区', '文三路 100 号', '310012')::addr, '31') AS ok_zj,
       -- 10 表示北京，不匹配
       valid_zip_prefix(ROW('浙江省', '杭州市', '西湖区', '文三路 100 号', '310012')::addr, '10') AS ok_bj;

text

 ok_zj | ok_bj
-------+-------
 t     | f
(1 row)

步骤 4：批量处理表数据

sql

-- 7. 表里存复合类型，查询时直接传给函数
DROP TABLE IF EXISTS shipping_addresses;

CREATE TABLE shipping_addresses (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  addr    addr   NOT NULL
);

INSERT INTO shipping_addresses (user_id, addr) VALUES
  (1, ROW('浙江省', '杭州市', '西湖区', '文三路 100 号', '310012')),
  (2, ROW('北京市', '北京市', '朝阳区', '建国路 88 号',   '100022')),
  (3, ROW('广东省', '深圳市', '南山区', '科技园路 1 号',  '518057'));

SELECT id,
       user_id,
       fmt_addr(addr, ' ') AS full_addr,
       (addr).province      AS province
FROM shipping_addresses
ORDER BY id;

text

 id | user_id |                    full_addr                     | province
----+---------+--------------------------------------------------+----------
  1 |       1 | 浙江省 杭州市 西湖区 文三路 100 号 (310012)      | 浙江省
  2 |       2 | 北京市 北京市 朝阳区 建国路 88 号 (100022)       | 北京市
  3 |       3 | 广东省 深圳市 南山区 科技园路 1 号 (518057)      | 广东省
(3 rows)

sql

-- 8. 对复合类型的某个字段建表达式索引
CREATE INDEX idx_ship_addr_province ON shipping_addresses (((addr).province));

EXPLAIN (COSTS OFF)
SELECT * FROM shipping_addresses WHERE (addr).province = '浙江省';

text

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Index Scan using idx_ship_addr_province on shipping_addresses
   Index Cond: ((addr).province = '浙江省'::text)
(2 rows)

案例三：自定义范围类型

sql

-- 1. 定义一个整数区间类型（演示范围类型语法）
DROP TYPE IF EXISTS int_span CASCADE;

CREATE TYPE int_span AS RANGE (
  SUBTYPE                = integer,
  SUBTYPE_DIFF           = int4mi,
  MULTIRANGE_TYPE_NAME   = int_span_multirange
);

-- 2. 用作商品价格区间（单位：分）
CREATE TABLE price_tiers (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text     NOT NULL,
  price    int_span NOT NULL
);

INSERT INTO price_tiers (name, price) VALUES
  ('低价区', '[0,10000)'::int_span),          -- 0 ~ 99.99 元
  ('中价区', '[10000,50000)'::int_span),      -- 100 ~ 499.99 元
  ('高价区', '[50000,)'::int_span);           -- 500 元以上

步骤 1：用 @> 判断某个价格落在哪个区间

sql

-- 3. 找出 29900 分（299 元）所属的区间
SELECT name, price
FROM price_tiers
WHERE price @> 29900;

text

  name  |   price
--------+------------
 中价区 | [10000,50000)
(1 row)

步骤 2：检查区间是否连续、有无重叠

sql

-- 4. 判断区间 [0,10000) 与 [10000,50000) 是否相邻（adjacent）
SELECT '[0,10000)'::int_span  -|- '[10000,50000)'::int_span AS adjacent_ok,
       '[0,10000)'::int_span  &&  '[10000,50000)'::int_span AS overlap;

text

 adjacent_ok | overlap
-------------+---------
 t           | f
(1 row)

步骤 3：为区间列建 GiST 索引

sql

-- 5. 范围类型支持 GiST 索引，加速 @> / && 查询
CREATE INDEX idx_price_tiers_range ON price_tiers USING GIST (price);

EXPLAIN (COSTS OFF)
SELECT name FROM price_tiers WHERE price @> 29900;

text

                                   QUERY PLAN
--------------------------------------------------------------------------------
 Bitmap Heap Scan on price_tiers
   Recheck Cond: (price @> 29900)
   ->  Bitmap Index Scan on idx_price_tiers_range
         Index Cond: (price @> 29900)
(4 rows)

注意事项

DROP TYPE 默认会被依赖阻塞。 加上 CASCADE 会连带删除使用该类型的列，这是不可逆的数据丢失，生产环境慎用。
枚举值一旦创建就无法删除。 只能新建类型 → ALTER TABLE 改列类型 → 删旧类型，大表会长时间持有 ACCESS EXCLUSIVE 锁。
枚举排序是声明顺序，不是字母序。 依赖默认排序的业务逻辑必须显式写 ORDER BY。
枚举不能与整数直接比较。 WHERE status = 2 会报类型错误，必须写 WHERE status = 'paid'。
给枚举加值到"中间位置"会重写表。 ADD VALUE ... BEFORE x 需要重新排序所有已存值，PostgreSQL 会重写表，大表上等同于一次 ALTER TABLE。
复合类型的字段名不能含大写。 不写成 CREATE TYPE t AS ("MyField" int) 就不会有加引号的麻烦，建议全小写加下划线。
复合类型在表中会产生"行膨胀"。 一个大复合类型 + TOAST 组合可能让单行体积翻倍，别把不常用的字段塞进复合类型。
(x).* 展开与 SELECT x.* 语义不同。 前者展开复合字段，后者展开表的所有列，前者务必加括号区分。
复合类型不支持默认值。 字段级别没有 DEFAULT 概念，默认值只能在外层表列上定义。
基类型必须用 C 实现 INPUT / OUTPUT。 这不是 SQL 能玩的东西，别把"用 SQL 造基类型"当成可选项。
域（Domain）与 CREATE TYPE 不是一回事。 域用 CREATE DOMAIN，且 pg_type.typtype 是 d，元命令是 \dD 而非 \dT。
类型的属主决定谁能改它。 跨 schema 复用的类型应显式 ALTER TYPE ... OWNER TO app_owner，否则属主变更会带来权限意外。
search_path 影响类型解析。 同名类型在多个 schema 中存在时，按 search_path 顺序取第一个，容易出错，建议给业务类型单独 schema 并显式限定。
类型名会成为数据的一部分。 一旦写入表，重命名类型需要同步更新所有依赖对象；改名前用 pg_depend 查清影响面。
不要为了"少写几列"而滥用复合类型。 表设计的第一原则仍是拆列，复合类型主要服务函数签名。

最佳实践

业务类型统一放独立 schema。 例如 types 或 app_types，避免与其他模块的类型重名。
每个类型都写 COMMENT ON TYPE。 说明语义、取值范围、是否允许扩展，这对团队协作收益极大。
枚举设计时预留冗余值。 宁可定义 reserved1、reserved2，也别等到线上要删值时才改架构。
状态机用枚举 + 显式流转函数。 把"哪些状态能转哪些"写进 PL/pgSQL 函数，比散落在应用代码里可靠得多。
复合类型只用于函数参数与返回值。 表中的业务数据老老实实拆列，索引与查询都会简单很多。
复合类型字段要建索引就用表达式索引。 CREATE INDEX ON t (((c).field))，注意双重括号。
复合类型的列名与字段名避免同义重复。 address.addr 这种命名既啰嗦又容易混淆。
改类型定义前先在测试库演练。 尤其是枚举加值到中间位置、复合类型加字段这两类会触发重建的操作。
用 pg_depend / pg_attribute 查依赖，不要靠猜。 删类型前必须确认影响面，用 CASCADE 前必须能说清会删掉什么。
范围类型优先用内置的。 int4range、numrange、tsrange、tstzrange、daterange 已覆盖绝大多数场景，自定义范围类型的收益通常很小。
迁移期用 IF NOT EXISTS 与 IF EXISTS。 CREATE TYPE IF NOT EXISTS（PostgreSQL 9.6+ 不支持，需用 DO 块判断）、ALTER TYPE ... ADD VALUE IF NOT EXISTS 可以让脚本可重复执行。
版本敏感特性写进注释。 例如 MULTIRANGE_TYPE_NAME（PG14+）、枚举在事务中 ADD VALUE（PG12+），团队成员的环境版本可能不一致。

继续学习：复合类型（Composite Type）

参考链接

PostgreSQL 官方文档：CREATE TYPE
PostgreSQL 官方文档：ALTER TYPE
PostgreSQL 官方文档：DROP TYPE
PostgreSQL 官方文档：Composite Types

CREATE TYPE 自定义类型 ​

基础概念 ​

为什么需要自定义类型 ​

CREATE TYPE 的四种形态 ​

自定义类型存在哪里 ​

类型的命名空间与可见性 ​

语法与用法 ​

复合类型 ​

枚举类型 ​

范围类型 ​

基类型（Shell Type） ​

域（Domain） ​

管理命令总表 ​

查看类型定义的三种方式 ​

详细说明 ​

复合类型的字段访问与构造 ​

复合类型 vs 拆列 vs JSONB ​

枚举的内部存储与排序 ​

ALTER TYPE 的能力边界 ​

依赖关系：为什么删不掉 ​

复合类型的修改是"重建级"的 ​

实战示例 ​

案例一：订单状态机（枚举类型） ​

CREATE TYPE 自定义类型

基础概念

为什么需要自定义类型

CREATE TYPE 的四种形态

自定义类型存在哪里

类型的命名空间与可见性

语法与用法

复合类型

枚举类型

范围类型

基类型（Shell Type）

域（Domain）

管理命令总表

查看类型定义的三种方式

详细说明

复合类型的字段访问与构造

复合类型 vs 拆列 vs JSONB

枚举的内部存储与排序

ALTER TYPE 的能力边界

依赖关系：为什么删不掉

复合类型的修改是"重建级"的

实战示例

案例一：订单状态机（枚举类型）