PostgreSQL 数据类型深入

CREATE TABLE articles (
    id    serial PRIMARY KEY,
    title text,
    tags  text[]          -- 文本数组
);

-- 字面量构造
INSERT INTO articles (title, tags)
VALUES ('PostgreSQL 入门', ARRAY['postgres', 'database', 'sql']);

-- 等价的花括号写法
INSERT INTO articles (title, tags)
VALUES ('索引原理', '{"postgres","index","btree"}');

-- 从查询构造
INSERT INTO articles (title, tags)
SELECT '汇总', array_agg(t) FROM unnest(ARRAY['a','b','c']) t;

SELECT tags[1] FROM articles;            -- 第一个元素
SELECT tags[1:2] FROM articles;          -- 切片，返回子数组
SELECT array_length(tags, 1) FROM articles;  -- 长度
SELECT cardinality(tags) FROM articles;      -- 元素总数

-- 查找包含 sql 标签的文章
SELECT title FROM articles WHERE tags @> ARRAY['sql'];

-- 查找含任一标签的文章
SELECT title FROM articles WHERE tags && ARRAY['index', 'btree'];

-- 展开数组为多行
SELECT id, unnest(tags) AS tag FROM articles;

-- 在数组中查找某值的位置
SELECT array_position(tags, 'sql') FROM articles;

CREATE INDEX idx_articles_tags ON articles USING gin (tags);

-- 现在 @> 与 && 都能走索引
EXPLAIN (ANALYZE)
SELECT title FROM articles WHERE tags @> ARRAY['sql'];

-- 坑 1：数组是可变长度，无法声明「最多 5 个」
-- 需要约束时用 CHECK
ALTER TABLE articles ADD CONSTRAINT tags_max5 CHECK (cardinality(tags) <= 5);

-- 坑 2：NULL 元素与 NULL 数组不同
SELECT ARRAY[1, NULL, 3];        -- 含 NULL 元素
SELECT NULL::int[];              -- 整个数组为 NULL

-- 坑 3：多维数组是矩形的，不能锯齿
SELECT ARRAY[[1,2],[3,4]];       -- 合法，2x2
-- SELECT ARRAY[[1,2],[3]];      -- 非法，维度不一致

int4range   整数区间
int8range   大整数区间
numrange    数值区间
tsrange     无时区时间戳区间
tstzrange   带时区时间戳区间
daterange   日期区间

CREATE TABLE room_bookings (
    id     serial PRIMARY KEY,
    room   text,
    period tstzrange,
    EXCLUDE USING gist (room WITH =, period WITH &&)   -- 同房间时间不可重叠
);

-- 左闭右开区间
INSERT INTO room_bookings (room, period)
VALUES ('A101', '[2026-10-01 09:00, 2026-10-01 11:00)');

-- 尝试插入重叠区间 → 被排他约束拒绝
INSERT INTO room_bookings (room, period)
VALUES ('A101', '[2026-10-01 10:00, 2026-10-01 12:00)');
-- ERROR: conflicting key value violates exclusion constraint

-- 查找某时刻被占用的房间
SELECT room FROM room_bookings
WHERE period @> '2026-10-01 10:30'::timestamptz;

-- 查找与给定区间重叠的预订
SELECT room FROM room_bookings
WHERE period && '[2026-10-01 10:00, 2026-10-01 11:00)'::tstzrange;

-- 复合排他：同房间 + 同类型，时间不可重叠
ALTER TABLE room_bookings ADD CONSTRAINT no_overlap
    EXCLUDE USING gist (room WITH =, period WITH &&);

-- 排他约束会自动创建 GiST 索引
-- 若只需查询加速，也可单独建
CREATE INDEX idx_bookings_period ON room_bookings USING gist (period);

CREATE TYPE address AS (
    street  text,
    city    text,
    zip     text,
    country text DEFAULT 'CN'
);

CREATE TABLE customers (
    id      serial PRIMARY KEY,
    name    text,
    addr    address
);

INSERT INTO customers (name, addr)
VALUES ('Acme', ROW('中山路 1 号', '北京', '100000', 'CN')::address);

-- 用点号访问字段，必须加括号
SELECT (addr).city, (addr).zip FROM customers;

-- 展开为多列
SELECT id, name, (addr).* FROM customers;

-- 按字段过滤
SELECT name FROM customers WHERE (addr).city = '北京';

- 强类型的地理坐标、金额、时间区间
- 函数返回多值（避免建临时表）
- 与 PL/pgSQL 配合封装业务对象

-- 复合类型字段无法单独建索引（只能整体或表达式索引）
CREATE INDEX idx_customers_city ON customers (((addr).city));

-- 复合类型无表级约束（外键等）
-- 复合类型不适合频繁单字段更新

-- 任意表的行类型都可以当作复合类型使用
SELECT row_to_json(c) FROM customers c;
SELECT (c).name FROM customers c;

CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id     serial PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');
-- INSERT INTO orders (status) VALUES ('unknown');   -- 报错，非法值

SELECT * FROM orders ORDER BY status;
-- pending < paid < shipped < delivered < cancelled

-- 添加新值（可指定位置）
ALTER TYPE order_status ADD VALUE 'refunded' BEFORE 'cancelled';

-- 注意：ALTER TYPE ADD VALUE 在旧版本不能在事务内使用
-- 且不能删除枚举值

-- 坑：枚举值删除不了，只能重建类型
-- 坑：枚举与文本比较需显式转换
SELECT * FROM orders WHERE status = 'paid';          -- 合法，字面量自动转
SELECT * FROM orders WHERE status::text = 'paid';    -- 也可，但会失去索引

CREATE EXTENSION hstore;

CREATE TABLE products (
    id       serial PRIMARY KEY,
    name     text,
    attrs    hstore
);

INSERT INTO products (name, attrs)
VALUES ('笔记本', 'brand=>ThinkPad, ram=>16GB, cpu=>i7');

-- 取键
SELECT attrs -> 'brand' FROM products;

-- 判断是否含键
SELECT name FROM products WHERE attrs ? 'ram';

-- 判断键值对
SELECT name FROM products WHERE attrs @> 'brand=>ThinkPad';

-- 展开为行
SELECT id, (each(attrs)).key, (each(attrs)).value FROM products;

-- 键列表
SELECT akeys(attrs) FROM products;

-- GIN 索引支持 ?、@> 等操作符
CREATE INDEX idx_products_attrs ON products USING gin (attrs);

-- 单键查询也可用表达式索引
CREATE INDEX idx_products_brand ON products ((attrs -> 'brand'));

-- 坑：hstore 的值只能是文本，数字需显式转换
SELECT (attrs -> 'ram')::text FROM products;   -- 返回 '16GB'

-- 坑：键不存在时返回 NULL，不会报错
SELECT attrs -> 'nonexistent' FROM products;   -- NULL

-- 查看列的平均宽度
SELECT attname, avg_width
FROM pg_stats
WHERE tablename = 'articles' AND attname = 'tags';

-- 数组包含
SELECT * FROM articles WHERE tags @> ARRAY['sql'];

-- 范围重叠
SELECT * FROM room_bookings WHERE period && '[2026-10-01, 2026-10-02)'::tstzrange;

-- 复合字段
SELECT * FROM customers WHERE (addr).city = '北京';

-- 枚举过滤
SELECT * FROM orders WHERE status = 'paid';

-- hstore 键存在
SELECT * FROM products WHERE attrs ? 'ram';

-- 数组转关联表（用 unnest 展开）
CREATE TABLE article_tags AS
SELECT id AS article_id, unnest(tags) AS tag FROM articles;

-- hstore 转 jsonb
ALTER TABLE products
    ALTER COLUMN attrs TYPE jsonb USING hstore_to_jsonb(attrs);

-- ========== 1. 数组类型 ==========
CREATE TABLE articles (
    id    serial PRIMARY KEY,
    title text,
    tags  text[]
);
CREATE INDEX idx_articles_tags ON articles USING gin (tags);

INSERT INTO articles (title, tags) VALUES
    ('PostgreSQL 入门', ARRAY['postgres', 'database', 'sql']),
    ('索引原理', '{"postgres","index","btree"}');

SELECT title FROM articles WHERE tags @> ARRAY['sql'];
SELECT title FROM articles WHERE tags && ARRAY['index', 'btree'];
SELECT id, unnest(tags) AS tag FROM articles;

-- ========== 2. 范围类型 + 排他约束 ==========
CREATE TABLE room_bookings (
    id     serial PRIMARY KEY,
    room   text,
    period tstzrange,
    EXCLUDE USING gist (room WITH =, period WITH &&)
);

INSERT INTO room_bookings (room, period)
VALUES ('A101', '[2026-10-01 09:00, 2026-10-01 11:00)');

SELECT room FROM room_bookings
WHERE period @> '2026-10-01 10:30'::timestamptz;

-- ========== 3. 复合类型 ==========
CREATE TYPE address AS (
    street  text,
    city    text,
    zip     text,
    country text DEFAULT 'CN'
);

CREATE TABLE customers (
    id   serial PRIMARY KEY,
    name text,
    addr address
);

INSERT INTO customers (name, addr)
VALUES ('Acme', ROW('中山路 1 号', '北京', '100000', 'CN')::address);

SELECT name, (addr).city, (addr).zip FROM customers;
CREATE INDEX idx_customers_city ON customers (((addr).city));

-- ========== 4. 枚举类型 ==========
CREATE TYPE order_status AS ENUM
    ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id     serial PRIMARY KEY,
    status order_status NOT NULL DEFAULT 'pending'
);

INSERT INTO orders (status) VALUES ('paid');
SELECT * FROM orders ORDER BY status;
ALTER TYPE order_status ADD VALUE 'refunded' BEFORE 'cancelled';

-- ========== 5. hstore ==========
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE products (
    id    serial PRIMARY KEY,
    name  text,
    attrs hstore
);
CREATE INDEX idx_products_attrs ON products USING gin (attrs);

INSERT INTO products (name, attrs)
VALUES ('笔记本', 'brand=>ThinkPad, ram=>16GB, cpu=>i7');

SELECT name FROM products WHERE attrs ? 'ram';
SELECT name FROM products WHERE attrs @> 'brand=>ThinkPad';
SELECT id, (each(attrs)).key, (each(attrs)).value FROM products;