02 PostgreSQL 数据类型与表设计

PostgreSQL 拥有关系型数据库中最丰富的数据类型系统，包括基础类型、高级类型（JSON/数组/范围/几何/网络）和自定义类型。本章系统介绍所有数据类型及表设计最佳实践。

2.1 数值类型

2.1.1 整数类型

类型	存储	范围	说明
`SMALLINT` / `INT2`	2 字节	-32768 ~ 32767	小整数
`INTEGER` / `INT` / `INT4`	4 字节	-21亿 ~ 21亿	最常用
`BIGINT` / `INT8`	8 字节	-922亿亿 ~ 922亿亿	大整数
`SMALLSERIAL`	2 字节	1 ~ 32767	自增小整数
`SERIAL`	4 字节	1 ~ 21亿	自增整数
`BIGSERIAL`	8 字节	1 ~ 922亿亿	自增大整数

sql

CREATE TABLE examples (
    small_num SMALLINT,
    normal_num INTEGER,
    big_num BIGINT,
    auto_id SERIAL,           -- 等价于 INTEGER + 序列
    auto_big_id BIGSERIAL     -- 等价于 BIGINT + 序列
);

-- SERIAL 的底层实现
-- CREATE TABLE t (id SERIAL) 等价于:
CREATE SEQUENCE t_id_seq;
CREATE TABLE t (
    id INTEGER NOT NULL DEFAULT nextval('t_id_seq')
);
ALTER SEQUENCE t_id_seq OWNED BY t.id;

SERIAL vs IDENTITY（PostgreSQL 10+）：

sql

-- 推荐方式（SQL 标准）
CREATE TABLE users (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- 或
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);

-- GENERATED ALWAYS: 不允许手动插入值
-- GENERATED BY DEFAULT: 允许手动插入值（推荐）

2.1.2 浮点数与定点数

类型	存储	精度	说明
`REAL` / `FLOAT4`	4 字节	6 位十进制精度	单精度浮点
`DOUBLE PRECISION` / `FLOAT8`	8 字节	15 位十进制精度	双精度浮点
`NUMERIC(p, s)` / `DECIMAL(p, s)`	可变	精确	金额必用
`NUMERIC`	可变	无限制	任意精度

sql

CREATE TABLE financial (
    price NUMERIC(10, 2),      -- 10 位总长，2 位小数
    amount DECIMAL(15, 4),     -- 15 位总长，4 位小数
    ratio REAL,                -- 单精度浮点
    precise_ratio DOUBLE PRECISION,  -- 双精度浮点
    arbitrary_precision NUMERIC      -- 任意精度
);

-- 金额计算示例
SELECT 0.1::NUMERIC + 0.2::NUMERIC;  -- 0.3（精确）
SELECT 0.1::REAL + 0.2::REAL;        -- 0.300000011920929（不精确）

-- 四舍五入
SELECT ROUND(123.456::NUMERIC, 2);   -- 123.46
SELECT TRUNC(123.456::NUMERIC, 2);   -- 123.45

数值类型选择建议：

场景	推荐类型	原因
金额、财务	`NUMERIC(10,2)`	精确，无舍入误差
ID、计数	`INTEGER` / `BIGINT`	性能好，空间小
科学计算	`DOUBLE PRECISION`	精度够用，性能好
比率、百分比	`NUMERIC(5,4)`	精确表示小数

2.2 字符类型

2.2.1 字符串类型

类型	说明	最大长度
`CHAR(n)` / `CHARACTER(n)`	定长字符串，不足补空格	n 个字符
`VARCHAR(n)` / `CHARACTER VARYING(n)`	变长字符串，有长度限制	n 个字符
`VARCHAR` / `TEXT`	变长字符串，无长度限制	1 GB

sql

CREATE TABLE string_examples (
    fixed_char CHAR(10),        -- 固定 10 字符，不足补空格
    limited_varchar VARCHAR(255),  -- 最多 255 字符
    unlimited_text TEXT          -- 无限制
);

-- CHAR 的陷阱
INSERT INTO string_examples (fixed_char) VALUES ('abc');
SELECT fixed_char, LENGTH(fixed_char), OCTET_LENGTH(fixed_char)
FROM string_examples;
-- 'abc       ', 10, 10  （补了 7 个空格）

-- 推荐：统一使用 TEXT 或 VARCHAR
-- PostgreSQL 中 TEXT 和 VARCHAR 性能完全相同
-- VARCHAR(n) 只用于业务上确实需要限制长度的场景

PostgreSQL vs MySQL 的字符类型差异：

特性	PostgreSQL	MySQL
`TEXT` 性能	与 `VARCHAR` 相同	比 `VARCHAR` 慢
`CHAR` 补空格	是	是
最大行大小	无限制（TOAST）	65535 字节
字符集	数据库级别	表/列级别

2.2.2 字符串函数

sql

-- 长度
LENGTH('hello');              -- 5（字符数）
CHAR_LENGTH('hello');         -- 5
OCTET_LENGTH('hello');        -- 5（字节数）
OCTET_LENGTH('你好');         -- 6（UTF-8 每个汉字 3 字节）

-- 大小写
UPPER('hello');               -- 'HELLO'
LOWER('HELLO');               -- 'hello'
INITCAP('hello world');       -- 'Hello World'

-- 截取
SUBSTRING('hello world' FROM 7);      -- 'world'
SUBSTRING('hello world' FROM 1 FOR 5); -- 'hello'
LEFT('hello', 3);             -- 'hel'
RIGHT('hello', 3);            -- 'llo'

-- 查找与替换
POSITION('world' IN 'hello world');   -- 7
STRPOS('hello world', 'world');       -- 7
REPLACE('hello world', 'world', 'pg'); -- 'hello pg'
REGEXP_REPLACE('hello123', '[0-9]+', 'NUM'); -- 'helloNUM'

-- 拼接
CONCAT('hello', ' ', 'world');        -- 'hello world'
CONCAT_WS('-', '2026', '09', '06');   -- '2026-09-06'
'hello' || ' ' || 'world';            -- 'hello world'

-- 填充与修剪
LPAD('42', 5, '0');           -- '00042'
RPAD('hi', 5, '!');           -- 'hi!!!'
TRIM('  hello  ');            -- 'hello'
LTRIM('  hello');             -- 'hello'
RTRIM('hello  ');             -- 'hello'
BTRIM('xxhelloxx', 'x');      -- 'hello'

-- 分割与聚合
STRING_TO_ARRAY('a,b,c', ',');        -- '{a,b,c}'
ARRAY_TO_STRING(ARRAY['a','b','c'], ','); -- 'a,b,c'
STRING_AGG(name, ', ') FROM users;    -- 'Alice, Bob, Charlie'

2.3 日期时间类型

2.3.1 时间类型

类型	存储	范围	说明
`DATE`	4 字节	4713 BC ~ 5874897 AD	日期
`TIME`	8 字节	00:00:00 ~ 24:00:00	时间（无时区）
`TIMETZ`	12 字节	同上	时间（带时区）
`TIMESTAMP`	8 字节	4713 BC ~ 294276 AD	时间戳（无时区）
`TIMESTAMPTZ`	8 字节	同上	时间戳（带时区，推荐）
`INTERVAL`	16 字节	-178000000年 ~ 178000000年	时间间隔

sql

CREATE TABLE time_examples (
    birth_date DATE,
    login_time TIME,
    created_at TIMESTAMP,        -- 不带时区
    updated_at TIMESTAMPTZ,      -- 带时区（推荐）
    duration INTERVAL
);

-- 当前时间
SELECT CURRENT_DATE;             -- 2026-09-06
SELECT CURRENT_TIME;             -- 15:30:00+08
SELECT CURRENT_TIMESTAMP;        -- 2026-09-06 15:30:00+08
SELECT NOW();                    -- 同 CURRENT_TIMESTAMP
SELECT LOCALTIME;                -- 本地时间（无时区）
SELECT LOCALTIMESTAMP;           -- 本地时间戳（无时区）

-- 时区处理
SET TIME ZONE 'Asia/Shanghai';
SELECT NOW();                    -- 2026-09-06 15:30:00+08

SET TIME ZONE 'UTC';
SELECT NOW();                    -- 2026-09-06 07:30:00+00

-- TIMESTAMPTZ 的优势
INSERT INTO time_examples (updated_at) VALUES ('2026-09-06 15:30:00+08');
SET TIME ZONE 'UTC';
SELECT updated_at FROM time_examples;  -- 2026-09-06 07:30:00+00
-- 自动转换时区，存储的是 UTC 时间

2.3.2 时间间隔（INTERVAL）

sql

-- 创建间隔
SELECT INTERVAL '1 year 2 months 3 days';
SELECT INTERVAL '1-2';            -- 1 year 2 months
SELECT INTERVAL '3 4:05:06';      -- 3 days 4 hours 5 minutes 6 seconds
SELECT INTERVAL '1.5 hours';      -- 1 hour 30 minutes

-- 时间运算
SELECT CURRENT_DATE + INTERVAL '1 day';      -- 明天
SELECT CURRENT_DATE - INTERVAL '1 month';   -- 上个月
SELECT CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes';

-- 提取部分
EXTRACT(YEAR FROM TIMESTAMP '2026-09-06 15:30:00');   -- 2026
EXTRACT(MONTH FROM TIMESTAMP '2026-09-06 15:30:00');  -- 9
EXTRACT(DOW FROM TIMESTAMP '2026-09-06 15:30:00');    -- 6（周日=0）
EXTRACT(EPOCH FROM TIMESTAMP '2026-09-06 15:30:00');  -- Unix 时间戳

-- 日期截断
DATE_TRUNC('year', TIMESTAMP '2026-09-06 15:30:00');   -- 2026-01-01 00:00:00
DATE_TRUNC('month', TIMESTAMP '2026-09-06 15:30:00'); -- 2026-09-01 00:00:00
DATE_TRUNC('day', TIMESTAMP '2026-09-06 15:30:00');   -- 2026-09-06 00:00:00

-- 年龄计算
SELECT AGE(TIMESTAMP '2000-01-01');  -- 26 years 8 mons 6 days
SELECT AGE(TIMESTAMP '2026-09-06', TIMESTAMP '2000-01-01');

2.3.3 时间格式化

sql

-- 格式化输出
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS');  -- '2026-09-06 15:30:00'
TO_CHAR(CURRENT_TIMESTAMP, 'YYYY年MM月DD日');         -- '2026年09月06日'
TO_CHAR(CURRENT_TIMESTAMP, 'Day, DD Month YYYY');     -- 'Sunday, 06 September 2026'

-- 常用格式符
-- YYYY: 4 位年份
-- MM: 2 位月份
-- DD: 2 位日期
-- HH24: 24 小时制小时
-- MI: 分钟
-- SS: 秒
-- Day: 星期全名
-- Mon: 月份缩写
-- DY: 星期缩写

-- 解析字符串为时间
TO_TIMESTAMP('2026-09-06 15:30:00', 'YYYY-MM-DD HH24:MI:SS');
TO_DATE('2026-09-06', 'YYYY-MM-DD');

2.4 布尔类型

sql

-- 布尔值
SELECT TRUE, FALSE, NULL;

-- 多种写法
SELECT 'true'::BOOLEAN;    -- true
SELECT 'yes'::BOOLEAN;     -- true
SELECT '1'::BOOLEAN;       -- true
SELECT 'on'::BOOLEAN;      -- true
SELECT 'false'::BOOLEAN;   -- false
SELECT 'no'::BOOLEAN;      -- false
SELECT '0'::BOOLEAN;       -- false
SELECT 'off'::BOOLEAN;     -- false

-- 使用场景
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    is_deleted BOOLEAN DEFAULT FALSE
);

-- 查询
SELECT * FROM users WHERE is_active;           -- 等同于 is_active = TRUE
SELECT * FROM users WHERE NOT is_deleted;      -- 未删除的用户
SELECT * FROM users WHERE is_active IS NOT NULL;

2.5 JSON 与 JSONB（重要）

2.5.1 JSON vs JSONB

特性	`JSON`	`JSONB`
存储格式	文本	二进制（分解后）
写入速度	快	略慢（需要解析）
查询速度	慢（每次解析）	快（已解析）
索引支持	不支持	支持 GIN 索引
保留空白	是	否
键顺序	保留	不保留（排序）
重复键	保留最后一个	保留最后一个
推荐	仅存储不查询	大多数场景

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    attributes JSONB         -- 使用 JSONB
);

-- 插入 JSON 数据
INSERT INTO products (name, attributes) VALUES
('iPhone 15', '{
    "color": "black",
    "storage": 256,
    "features": ["5G", "Face ID", "Wireless charging"],
    "dimensions": {"width": 71.6, "height": 147.6, "depth": 7.8}
}'),
('MacBook Pro', '{
    "color": "silver",
    "cpu": "M3 Pro",
    "ram": 18,
    "features": ["Retina display", "Thunderbolt 4"]
}');

2.5.2 JSONB 操作符

sql

-- 访问操作符
SELECT attributes->'color' FROM products WHERE name = 'iPhone 15';
-- "black"（返回 JSONB）

SELECT attributes->>'color' FROM products WHERE name = 'iPhone 15';
-- 'black'（返回 TEXT）

SELECT attributes->'dimensions'->>'width' FROM products WHERE name = 'iPhone 15';
-- '71.6'

SELECT attributes#>'{dimensions,width}' FROM products WHERE name = 'iPhone 15';
-- 71.6（路径访问）

SELECT attributes#>>'{dimensions,width}' FROM products WHERE name = 'iPhone 15';
-- '71.6'

-- 数组访问
SELECT attributes->'features'->0 FROM products WHERE name = 'iPhone 15';
-- "5G"

SELECT attributes->'features'->>0 FROM products WHERE name = 'iPhone 15';
-- '5G'

-- 包含操作符
SELECT * FROM products WHERE attributes @> '{"color": "black"}';
-- 包含指定 JSON

SELECT * FROM products WHERE attributes ? 'color';
-- 包含指定键

SELECT * FROM products WHERE attributes ?| ARRAY['color', 'storage'];
-- 包含任一指定键

SELECT * FROM products WHERE attributes ?& ARRAY['color', 'storage'];
-- 包含所有指定键

2.5.3 JSONB 修改

sql

-- 更新/添加键值
UPDATE products
SET attributes = attributes || '{"weight": 187}'
WHERE name = 'iPhone 15';

-- 删除键
UPDATE products
SET attributes = attributes - 'weight'
WHERE name = 'iPhone 15';

-- 删除多个键
UPDATE products
SET attributes = attributes - ARRAY['weight', 'dimensions']
WHERE name = 'iPhone 15';

-- 删除数组元素（按索引）
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{features}',
    (attributes->'features') - 0  -- 删除第一个元素
)
WHERE name = 'iPhone 15';

-- 使用 jsonb_set 更新
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{storage}',
    '512'
)
WHERE name = 'iPhone 15';

-- 使用 jsonb_set 添加数组元素
UPDATE products
SET attributes = jsonb_set(
    attributes,
    '{features,999}',  -- 999 表示追加到末尾
    '"NFC"'
)
WHERE name = 'iPhone 15';

2.5.4 JSONB 索引

sql

-- GIN 索引（全文搜索）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- 查询时使用索引
EXPLAIN SELECT * FROM products WHERE attributes @> '{"color": "black"}';
-- Bitmap Heap Scan on products
--   Recheck Cond: (attributes @> '{"color": "black"}'::jsonb)
--   ->  Bitmap Index Scan on idx_products_attributes

-- GIN 索引支持的操作符
-- @> (包含)
-- <@ (被包含)
-- ? (键存在)
-- ?| (任一键存在)
-- ?& (所有键存在)

-- 部分索引（只索引特定路径）
CREATE INDEX idx_products_color ON products ((attributes->>'color'));

-- 使用时
SELECT * FROM products WHERE attributes->>'color' = 'black';
-- 使用 idx_products_color 索引

2.5.5 JSONB 聚合函数

sql

-- 聚合为 JSON 对象
SELECT jsonb_object_agg(name, attributes->>'color')
FROM products;
-- {"iPhone 15": "black", "MacBook Pro": "silver"}

-- 聚合为 JSON 数组
SELECT jsonb_agg(name) FROM products;
-- ["iPhone 15", "MacBook Pro"]

-- 展开 JSON 数组
SELECT jsonb_array_elements(attributes->'features')
FROM products WHERE name = 'iPhone 15';
-- "5G"
-- "Face ID"
-- "Wireless charging"

-- 展开为多行
SELECT p.name, feature
FROM products p,
     jsonb_array_elements_text(p.attributes->'features') AS feature;

2.6 数组类型

2.6.1 数组基础

sql

-- 创建数组列
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    scores INTEGER[],          -- 整数数组
    tags TEXT[] DEFAULT '{}'   -- 文本数组，默认空数组
);

-- 插入数组
INSERT INTO students (name, scores, tags) VALUES
('Alice', ARRAY[95, 87, 92], ARRAY['math', 'physics']),
('Bob', '{88, 91, 85}', '{chemistry, biology}');

-- 数组字面量语法
SELECT ARRAY[1, 2, 3];        -- {1,2,3}
SELECT '{1, 2, 3}'::INTEGER[]; -- {1,2,3}

-- 访问数组元素（索引从 1 开始！）
SELECT scores[1] FROM students WHERE name = 'Alice';  -- 95（第一个元素）
SELECT scores[2] FROM students WHERE name = 'Alice';  -- 87

-- 数组切片
SELECT scores[1:2] FROM students WHERE name = 'Alice';  -- {95,87}

-- 数组长度
SELECT array_length(scores, 1) FROM students WHERE name = 'Alice';  -- 3

-- 数组维度
SELECT array_ndims(scores) FROM students WHERE name = 'Alice';  -- 1

2.6.2 数组操作

sql

-- 追加元素
UPDATE students SET tags = tags || 'coding' WHERE name = 'Alice';
-- 或
UPDATE students SET tags = array_append(tags, 'coding') WHERE name = 'Alice';

-- 前置元素
UPDATE students SET tags = 'honor' || tags WHERE name = 'Alice';
-- 或
UPDATE students SET tags = array_prepend('honor', tags) WHERE name = 'Alice';

-- 删除元素
UPDATE students SET tags = array_remove(tags, 'math') WHERE name = 'Alice';

-- 连接数组
SELECT ARRAY[1,2,3] || ARRAY[4,5,6];  -- {1,2,3,4,5,6}
SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);  -- {1,2,3,4}

-- 数组比较
SELECT ARRAY[1,2,3] = ARRAY[1,2,3];  -- true
SELECT ARRAY[1,2,3] @> ARRAY[1,2];   -- true（包含）
SELECT ARRAY[1,2] <@ ARRAY[1,2,3];   -- true（被包含）
SELECT ARRAY[1,2,3] && ARRAY[3,4];   -- true（有交集）

-- 查询包含特定元素的行
SELECT * FROM students WHERE 'math' = ANY(tags);
SELECT * FROM students WHERE tags @> ARRAY['math'];
SELECT * FROM students WHERE tags && ARRAY['math', 'physics'];  -- 包含任一

2.6.3 数组函数

sql

-- 展开数组为多行
SELECT unnest(ARRAY[1, 2, 3]);
-- 1
-- 2
-- 3

-- 与表连接
SELECT s.name, score
FROM students s, unnest(s.scores) AS score;

-- 聚合为数组
SELECT array_agg(name) FROM students;
-- {Alice,Bob}

-- 数组转字符串
SELECT array_to_string(ARRAY['a', 'b', 'c'], ', ');
-- 'a, b, c'

-- 字符串转数组
SELECT string_to_array('a,b,c', ',');
-- {a,b,c}

-- 数组位置
SELECT array_position(ARRAY['a', 'b', 'c'], 'b');  -- 2

-- 数组去重
SELECT ARRAY(SELECT DISTINCT unnest(ARRAY[1, 2, 2, 3]));
-- {1,2,3}

2.6.4 数组索引

sql

-- GIN 索引（支持 @>、<@、&& 操作符）
CREATE INDEX idx_students_tags ON students USING GIN (tags);

-- 使用时
SELECT * FROM students WHERE tags @> ARRAY['math'];  -- 使用索引
SELECT * FROM students WHERE tags && ARRAY['math', 'physics'];  -- 使用索引

-- 注意：= ANY() 不使用 GIN 索引
-- 改用 @> 操作符

2.7 范围类型（Range）

2.7.1 内置范围类型

类型	说明
`INT4RANGE`	整数范围
`INT8RANGE`	大整数范围
`NUMRANGE`	数值范围
`TSRANGE`	时间戳范围（无时区）
`TSTZRANGE`	时间戳范围（带时区）
`DATERANGE`	日期范围

sql

-- 创建范围
SELECT int4range(10, 20);      -- [10,20)
SELECT int4range(10, 20, '[]'); -- [10,20]（包含两端）
SELECT int4range(10, 20, '(]'); -- (10,20]
SELECT int4range(10, NULL);    -- [10,)（无上限）
SELECT int4range(NULL, 20);    -- (,20)（无下限）

-- 范围操作符
SELECT int4range(10, 20) @> 15;     -- true（包含元素）
SELECT int4range(10, 20) @> int4range(12, 15);  -- true（包含范围）
SELECT int4range(10, 20) && int4range(15, 25);  -- true（有重叠）
SELECT int4range(10, 20) << int4range(25, 30);  -- true（严格在左）
SELECT int4range(10, 20) >> int4range(5, 8);    -- true（严格在右）
SELECT int4range(10, 20) -|- int4range(20, 30); -- true（相邻）

-- 范围运算
SELECT int4range(10, 20) + int4range(15, 25);   -- [10,25)（并集）
SELECT int4range(10, 20) * int4range(15, 25);   -- [15,20)（交集）
SELECT int4range(10, 20) - int4range(15, 25);   -- [10,15)（差集）

2.7.2 实际应用：预约系统

sql

CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    during TSTZRANGE NOT NULL,
    guest_name TEXT NOT NULL,

    -- 防止同一房间的预约时间重叠
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

-- 插入预约
INSERT INTO reservations (room_id, during, guest_name) VALUES
(101, '[2026-09-06 14:00:00+08, 2026-09-06 16:00:00+08)', 'Alice'),
(101, '[2026-09-06 16:00:00+08, 2026-09-06 18:00:00+08)', 'Bob');

-- 尝试插入重叠的预约（会失败）
INSERT INTO reservations (room_id, during, guest_name) VALUES
(101, '[2026-09-06 15:00:00+08, 2026-09-06 17:00:00+08)', 'Charlie');
-- ERROR: conflicting key value violates exclusion constraint

-- 查询某时间段的预约
SELECT * FROM reservations
WHERE during && '[2026-09-06 15:00:00+08, 2026-09-06 16:00:00+08)';

2.8 几何与网络类型

2.8.1 几何类型

sql

-- 点
SELECT point '(1, 2)';

-- 线
SELECT line '{1, 2, 3}';  -- Ax + By + C = 0

-- 线段
SELECT lseg '[(1,2), (3,4)]';

-- 盒子
SELECT box '((1,2), (3,4))';

-- 路径
SELECT path '[(1,2), (3,4), (5,6)]';  -- 开放路径
SELECT path '((1,2), (3,4), (5,6))';  -- 闭合路径

-- 多边形
SELECT polygon '((1,2), (3,4), (5,6))';

-- 圆
SELECT circle '<(1,2), 3>';  -- 圆心 (1,2)，半径 3

-- 几何运算
SELECT point '(1, 2)' <-> point '(4, 6)';  -- 距离：5
SELECT box '((1,2), (3,4))' && box '((2,3), (5,6))';  -- 是否重叠
SELECT point '(2, 3)' <@ box '((1,2), (3,4))';  -- 点是否在盒子内

2.8.2 网络地址类型

sql

-- IP 地址
CREATE TABLE network_devices (
    id SERIAL PRIMARY KEY,
    ip INET,              -- IP 地址（带或不带子网掩码）
    network CIDR,         -- 网络地址（带子网掩码）
    mac MACADDR           -- MAC 地址
);

INSERT INTO network_devices (ip, network, mac) VALUES
('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03'),
('192.168.1.100/24', '192.168.1.0/24', '08:00:2b:01:02:04'),
('10.0.0.1', '10.0.0.0/8', '08:00:2b:01:02:05');

-- IP 地址操作
SELECT '192.168.1.100'::INET + 1;  -- 192.168.1.101
SELECT '192.168.1.100'::INET - '192.168.1.50'::INET;  -- 50（差值）
SELECT host('192.168.1.100/24'::INET);  -- '192.168.1.100'
SELECT masklen('192.168.1.100/24'::INET);  -- 24
SELECT family('192.168.1.100'::INET);  -- 4（IPv4）
SELECT family('::1'::INET);  -- 6（IPv6）

-- 子网包含
SELECT '192.168.1.100'::INET << '192.168.1.0/24'::CIDR;  -- true
SELECT '192.168.1.0/24'::CIDR >> '192.168.1.100'::INET;  -- true

-- 查询同一子网的设备
SELECT * FROM network_devices
WHERE ip << '192.168.1.0/24'::CIDR;

2.9 UUID 类型

sql

-- 需要 uuid-ossp 扩展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER REFERENCES users(id),
    token TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入
INSERT INTO sessions (user_id, token) VALUES (1, 'abc123');

-- UUID 函数
SELECT uuid_generate_v1();  -- 基于时间和 MAC 地址
SELECT uuid_generate_v4();  -- 随机（最常用）

-- PostgreSQL 13+ 内置 gen_random_uuid()
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

UUID vs SERIAL：

特性	UUID	SERIAL
存储	16 字节	4/8 字节
可读性	差	好
分布式	✅ 全局唯一	❌ 需要协调
索引性能	较差（随机）	好（顺序）
安全性	不暴露信息	暴露数量

2.10 枚举类型（ENUM）

sql

-- 创建枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

-- 使用枚举
CREATE TABLE diary (
    id SERIAL PRIMARY KEY,
    entry_date DATE NOT NULL,
    feeling mood NOT NULL,
    note TEXT
);

INSERT INTO diary (entry_date, feeling, note) VALUES
('2026-09-06', 'happy', 'Great day!'),
('2026-09-05', 'ok', 'Normal day'),
('2026-09-04', 'sad', 'Bad day');

-- 枚举比较（按定义顺序）
SELECT * FROM diary WHERE feeling > 'ok';  -- 'happy'

-- 添加枚举值（不能删除或重排序）
ALTER TYPE mood ADD VALUE 'excited' AFTER 'happy';

-- 查看所有枚举值
SELECT enum_range(NULL::mood);
-- {sad,ok,happy,excited}

2.11 表设计最佳实践

2.11.1 命名规范

sql

-- 表名：小写 + 下划线，复数形式
CREATE TABLE users (...);
CREATE TABLE order_items (...);

-- 列名：小写 + 下划线
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email_address TEXT,
    created_at TIMESTAMPTZ
);

-- 避免保留字
-- ❌ CREATE TABLE order (...)  -- order 是保留字
-- ✅ CREATE TABLE orders (...)

-- 外键列名：关联表名 + _id
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),  -- 关联 users 表
    category_id INTEGER REFERENCES categories(id)
);

2.11.2 主键设计

sql

-- 方式 1：SERIAL（传统）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    ...
);

-- 方式 2：IDENTITY（推荐，PostgreSQL 10+）
CREATE TABLE users (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ...
);

-- 方式 3：UUID（分布式场景）
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

-- 方式 4：复合主键
CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

2.11.3 时间戳字段

sql

-- 推荐：使用 TIMESTAMPTZ
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 自动更新 updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

2.11.4 软删除

sql

-- 添加 deleted_at 字段
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    deleted_at TIMESTAMPTZ  -- NULL 表示未删除
);

-- 查询未删除的记录
SELECT * FROM users WHERE deleted_at IS NULL;

-- 创建部分索引（优化查询）
CREATE INDEX idx_users_active ON users(id) WHERE deleted_at IS NULL;

-- 使用视图简化查询
CREATE VIEW active_users AS
SELECT * FROM users WHERE deleted_at IS NULL;

2.11.5 约束设计

sql

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    sku TEXT UNIQUE NOT NULL,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,

    -- 复合约束
    CONSTRAINT valid_price_range CHECK (price > 0 AND price < 1000000),

    -- 排除约束（PostgreSQL 特有）
    EXCLUDE USING GIST (
        name WITH =,
        category_id WITH =
    ) WHERE (deleted_at IS NULL)  -- 同分类下名称唯一（仅活跃记录）
);

-- 外键约束选项
-- ON DELETE CASCADE: 删除父记录时级联删除子记录
-- ON DELETE SET NULL: 删除父记录时设为 NULL
-- ON DELETE SET DEFAULT: 删除父记录时设为默认值
-- ON DELETE RESTRICT: 阻止删除（默认）
-- ON DELETE NO ACTION: 类似 RESTRICT，但可延迟检查

本章小结

PostgreSQL 拥有最丰富的数据类型系统，包括基础类型、JSON/JSONB、数组、范围、几何、网络等
JSONB 是存储半结构化数据的首选，支持 GIN 索引和丰富操作符
数组类型 适合存储标签、评分等多值属性
范围类型 适合时间预约、价格区间等场景
TIMESTAMPTZ 是时间戳的推荐类型，自动处理时区
IDENTITY 是 SERIAL 的现代替代方案（PostgreSQL 10+）
表设计遵循命名规范，使用约束保证数据完整性

练习：把「02 PostgreSQL 数据类型与表设计」变成可验证能力

从本文任选一个示例，在可丢弃环境重跑；记录运行时/服务版本、输入、关键输出和清理动作。
保持其他条件不变，只制造一个边界：空值、重复请求、并发、超时、断连、权限不足或资源耗尽。
用四句话复盘：现象是什么、原理在哪一层、证据是什么、生产中如何止损。

验收清单

[ ] 示例能从空环境重复执行，必要依赖和版本已写明。
[ ] 同时保留成功路径与至少一个失败路径，不用“应该没问题”冒充证据。
[ ] 结论写明适用边界，并能链接回对应学习路线的能力域。
[ ] 练习结束后已清理临时数据、凭据、端口或进程。

对照数据库练习答案与复盘尺。先交自己的证据再翻答案；答案是镜子，不是传送门。

02 PostgreSQL 数据类型与表设计 ​

2.1 数值类型 ​

2.1.1 整数类型 ​

2.1.2 浮点数与定点数 ​

2.2 字符类型 ​

2.2.1 字符串类型 ​

2.2.2 字符串函数 ​

2.3 日期时间类型 ​

2.3.1 时间类型 ​

2.3.2 时间间隔（INTERVAL） ​

2.3.3 时间格式化 ​

2.4 布尔类型 ​

2.5 JSON 与 JSONB（重要） ​

2.5.1 JSON vs JSONB ​

2.5.2 JSONB 操作符 ​

2.5.3 JSONB 修改 ​

2.5.4 JSONB 索引 ​

2.5.5 JSONB 聚合函数 ​

2.6 数组类型 ​

2.6.1 数组基础 ​

2.6.2 数组操作 ​

2.6.3 数组函数 ​

2.6.4 数组索引 ​

2.7 范围类型（Range） ​

2.7.1 内置范围类型 ​

2.7.2 实际应用：预约系统 ​

2.8 几何与网络类型 ​

2.8.1 几何类型 ​

2.8.2 网络地址类型 ​

2.9 UUID 类型 ​

2.10 枚举类型（ENUM） ​

2.11 表设计最佳实践 ​

2.11.1 命名规范 ​

2.11.2 主键设计 ​

2.11.3 时间戳字段 ​

2.11.4 软删除 ​

2.11.5 约束设计 ​

本章小结 ​

练习：把「02 PostgreSQL 数据类型与表设计」变成可验证能力 ​