pgsql-03 数据类型详解

2026-03-16 约 4540 字预计阅读 10 分钟

03 - 数据类型详解

1. 📖 概述

PostgreSQL拥有非常丰富的数据类型系统，远超MySQL。本章将详细介绍PostgreSQL的各种数据类型，并与MySQL进行对比。

2. 🔢 数值类型

2.1 整数类型

PostgreSQL类型	MySQL类型	存储大小	范围	说明
SMALLINT	SMALLINT	2字节	-32768 ~ 32767	小整数
INTEGER (INT)	INT	4字节	-2147483648 ~ 2147483647	标准整数
BIGINT	BIGINT	8字节	-9223372036854775808 ~ 9223372036854775807	大整数
SERIAL	-	4字节	1 ~ 2147483647	自增整数
BIGSERIAL	-	8字节	1 ~ 9223372036854775807	大自增整数
SMALLSERIAL	-	2字节	1 ~ 32767	小自增整数

-- PostgreSQL
CREATE TABLE products (
    id SERIAL PRIMARY KEY,              -- 自增主键
    stock_quantity SMALLINT,            -- 库存数量
    views INTEGER,                      -- 浏览次数
    sales_count BIGINT                  -- 销售总数
);

-- MySQL对比
-- CREATE TABLE products (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     stock_quantity SMALLINT,
--     views INT,
--     sales_count BIGINT
-- );

SERIAL详解：

-- PostgreSQL的SERIAL实际上是以下的简写：
CREATE SEQUENCE products_id_seq;
CREATE TABLE products (
    id INTEGER NOT NULL DEFAULT nextval('products_id_seq'),
    ...
);
ALTER SEQUENCE products_id_seq OWNED BY products.id;

2.2 浮点数类型

PostgreSQL类型	MySQL类型	存储大小	精度	说明
REAL	FLOAT	4字节	6位小数	单精度浮点
DOUBLE PRECISION	DOUBLE	8字节	15位小数	双精度浮点
NUMERIC(p,s)	DECIMAL(p,s)	可变	用户指定	精确小数
DECIMAL(p,s)	DECIMAL(p,s)	可变	用户指定	NUMERIC别名

-- PostgreSQL
CREATE TABLE financial_data (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),               -- 价格：10位数字，2位小数
    weight REAL,                        -- 重量：单精度
    calculation DOUBLE PRECISION,       -- 计算结果：双精度
    interest_rate NUMERIC(5, 4)         -- 利率：如 0.0525
);

-- 插入示例
INSERT INTO financial_data (price, weight, calculation, interest_rate)
VALUES (99.99, 1.5, 3.141592653589793, 0.0525);

-- MySQL完全相同

注意事项：

金融计算必须使用NUMERIC，不能用REAL或DOUBLE（避免精度问题）
NUMERIC没有精度限制，但会占用更多空间
NUMERIC(10,2)表示总共10位数字，其中2位小数（如 12345678.90）

2.3 自增序列

-- PostgreSQL：手动创建序列
CREATE SEQUENCE order_number_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    CACHE 10;

-- 使用序列
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_number INTEGER DEFAULT nextval('order_number_seq')
);

-- 获取下一个值
SELECT nextval('order_number_seq');

-- 获取当前值
SELECT currval('order_number_seq');

-- 设置序列值
SELECT setval('order_number_seq', 5000);

-- MySQL没有独立序列，只能使用AUTO_INCREMENT
-- CREATE TABLE orders (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     order_number INT
-- );
-- ALTER TABLE orders AUTO_INCREMENT = 1000;

3. 📝 字符串类型

3.1 基本字符串类型

PostgreSQL类型	MySQL类型	存储限制	说明
VARCHAR(n)	VARCHAR(n)	最大1GB	变长字符串，限制长度
CHAR(n)	CHAR(n)	最大1GB	定长字符串，空格填充
TEXT	TEXT	最大1GB	无限制变长字符串
NAME	-	64字节	内部对象名称

-- PostgreSQL
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,      -- 变长，最多50字符
    password_hash CHAR(64),             -- 定长，SHA-256哈希
    bio TEXT,                           -- 个人简介，无长度限制
    country_code CHAR(2)                -- 固定2字符，如'US'
);

-- MySQL类似，但TEXT类型有不同的变体
-- CREATE TABLE users (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     username VARCHAR(50) NOT NULL,
--     password_hash CHAR(64),
--     bio TEXT,                        -- MySQL: TEXT, MEDIUMTEXT, LONGTEXT
--     country_code CHAR(2)
-- );

PostgreSQL字符串特点：

VARCHAR和TEXT性能基本相同
CHAR(n)会用空格填充到n长度
没有MySQL的TINYTEXT、MEDIUMTEXT、LONGTEXT区分
字符串操作区分大小写（可用CITEXT扩展实现不区分大小写）

3.2 字符串操作示例

-- PostgreSQL字符串函数
SELECT
    LENGTH('Hello') AS len,                    -- 5
    UPPER('hello') AS upper_case,              -- HELLO
    LOWER('WORLD') AS lower_case,              -- world
    SUBSTRING('PostgreSQL' FROM 1 FOR 8),      -- Postgres
    CONCAT('Hello', ' ', 'World'),             -- Hello World
    'Hello' || ' ' || 'World' AS concat_op,    -- Hello World（推荐）
    POSITION('SQL' IN 'PostgreSQL'),           -- 9
    REPLACE('Hello World', 'World', 'PG'),     -- Hello PG
    TRIM('  hello  ');                         -- hello

-- 正则表达式匹配
SELECT 'hello' ~ '^h.*o$';                     -- true
SELECT REGEXP_REPLACE('Hello123', '\d+', 'XXX'); -- HelloXXX

4. 📅 日期时间类型

4.1 日期时间类型对比

PostgreSQL类型	MySQL类型	存储大小	范围	精度
TIMESTAMP	DATETIME	8字节	4713 BC ~ 294276 AD	微秒
TIMESTAMP WITH TIME ZONE	-	8字节	同上	微秒
DATE	DATE	4字节	4713 BC ~ 5874897 AD	日
TIME	TIME	8字节	00:00:00 ~ 24:00:00	微秒
TIME WITH TIME ZONE	-	12字节	同上	微秒
INTERVAL	-	16字节	-178000000年 ~ 178000000年	微秒

-- PostgreSQL
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    event_name VARCHAR(100),
    event_date DATE,                           -- 只存日期
    event_time TIME,                           -- 只存时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 时间戳（无时区）
    updated_at TIMESTAMPTZ DEFAULT NOW(),      -- 时间戳（带时区）
    duration INTERVAL                          -- 时间间隔
);

-- MySQL对比
-- CREATE TABLE events (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     event_name VARCHAR(100),
--     event_date DATE,
--     event_time TIME,
--     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
--     -- MySQL没有INTERVAL类型
-- );

4.2 时区处理（PostgreSQL独有）

-- PostgreSQL：带时区的时间戳
CREATE TABLE global_events (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    starts_at TIMESTAMPTZ,                     -- 推荐使用TIMESTAMPTZ
    ends_at TIMESTAMPTZ
);

-- 插入不同时区的数据
INSERT INTO global_events (title, starts_at) VALUES
    ('New York Event', '2026-03-15 10:00:00-05'),    -- 美东时间
    ('London Event', '2026-03-15 15:00:00+00'),      -- UTC时间
    ('Tokyo Event', '2026-03-16 00:00:00+09');       -- 日本时间

-- 查询时自动转换为当前时区
SET timezone = 'America/New_York';
SELECT title, starts_at FROM global_events;

-- 转换时区
SELECT
    starts_at AT TIME ZONE 'UTC' AS utc_time,
    starts_at AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
FROM global_events;

4.3 INTERVAL类型（PostgreSQL独有）

-- PostgreSQL：时间间隔运算
SELECT
    NOW() + INTERVAL '1 day' AS tomorrow,
    NOW() - INTERVAL '1 week' AS last_week,
    NOW() + INTERVAL '2 hours 30 minutes' AS later,
    DATE '2026-03-15' + INTEGER '7' AS next_week,
    AGE(TIMESTAMP '2026-01-01', TIMESTAMP '2000-01-01') AS age;

-- 存储时间间隔
UPDATE events SET duration = INTERVAL '2 hours 15 minutes' WHERE id = 1;

-- 计算两个时间之间的间隔
SELECT
    event_name,
    AGE(updated_at, created_at) AS elapsed_time
FROM events;

4.4 日期时间函数

-- PostgreSQL日期时间函数
SELECT
    CURRENT_DATE,                              -- 当前日期
    CURRENT_TIME,                              -- 当前时间
    CURRENT_TIMESTAMP,                         -- 当前时间戳
    NOW(),                                     -- 当前时间戳（推荐）
    EXTRACT(YEAR FROM NOW()),                  -- 提取年份
    EXTRACT(MONTH FROM NOW()),                 -- 提取月份
    EXTRACT(DAY FROM NOW()),                   -- 提取日
    DATE_PART('hour', NOW()),                  -- 提取小时
    DATE_TRUNC('day', NOW()),                  -- 截断到天
    TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'),  -- 格式化
    TO_TIMESTAMP('2026-03-15', 'YYYY-MM-DD');  -- 字符串转时间戳

-- MySQL对比
-- SELECT
--     CURDATE(),                              -- 当前日期
--     CURTIME(),                              -- 当前时间
--     NOW(),                                  -- 当前时间戳
--     YEAR(NOW()),                            -- 提取年份
--     MONTH(NOW()),                           -- 提取月份
--     DAY(NOW()),                             -- 提取日
--     HOUR(NOW()),                            -- 提取小时
--     DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'), -- 格式化
--     STR_TO_DATE('2026-03-15', '%Y-%m-%d');  -- 字符串转日期

5. 🔤 布尔类型

-- PostgreSQL：真正的布尔类型
CREATE TABLE features (
    id SERIAL PRIMARY KEY,
    is_active BOOLEAN DEFAULT TRUE,
    is_premium BOOLEAN DEFAULT FALSE
);

-- 插入数据（支持多种表示方式）
INSERT INTO features (is_active, is_premium) VALUES
    (TRUE, FALSE),
    ('yes', 'no'),        -- yes/no
    ('on', 'off'),        -- on/off
    ('1', '0'),           -- 1/0
    ('t', 'f');           -- t/f

-- 查询
SELECT * FROM features WHERE is_active = TRUE;
SELECT * FROM features WHERE is_active;      -- 简写
SELECT * FROM features WHERE NOT is_premium;

-- MySQL：使用TINYINT(1)模拟布尔
-- CREATE TABLE features (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     is_active TINYINT(1) DEFAULT 1,
--     is_premium TINYINT(1) DEFAULT 0
-- );
-- MySQL的BOOLEAN实际上是TINYINT(1)的别名

6. 📦 二进制类型

PostgreSQL类型	MySQL类型	说明
BYTEA	BLOB	二进制数据

-- PostgreSQL
CREATE TABLE file_storage (
    id SERIAL PRIMARY KEY,
    filename VARCHAR(255),
    file_data BYTEA,
    file_size INTEGER
);

-- 插入二进制数据
INSERT INTO file_storage (filename, file_data)
VALUES ('logo.png', '\xDEADBEEF'::bytea);

-- MySQL对比
-- CREATE TABLE file_storage (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     filename VARCHAR(255),
--     file_data BLOB,  -- TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
--     file_size INT
-- );

7. 🎨 PostgreSQL特色类型

7.1 UUID类型

-- PostgreSQL：UUID（通用唯一标识符）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

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

-- 插入数据
INSERT INTO sessions (user_id) VALUES (1);

-- 手动指定UUID
INSERT INTO sessions (id, user_id)
VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 2);

-- MySQL需要使用CHAR(36)存储UUID字符串
-- CREATE TABLE sessions (
--     id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
--     user_id INT,
--     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- );

7.2 JSON类型

-- PostgreSQL：JSON和JSONB
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    metadata JSON,                    -- JSON文本
    preferences JSONB                 -- 二进制JSON（推荐）
);

-- 插入JSON数据
INSERT INTO user_profiles (username, metadata, preferences) VALUES
    ('alice', '{"age": 25, "city": "NYC"}',
     '{"theme": "dark", "notifications": true}');

-- 查询JSON数据
SELECT
    username,
    preferences->>'theme' AS theme,                  -- 提取文本
    preferences->'notifications' AS notifications,   -- 提取JSON
    metadata->'age' AS age
FROM user_profiles;

-- JSONB索引和查询
CREATE INDEX idx_preferences ON user_profiles USING GIN (preferences);

SELECT * FROM user_profiles WHERE preferences @> '{"theme": "dark"}';

JSON vs JSONB对比：

特性	JSON	JSONB
存储方式	文本	二进制
插入速度	快	慢（需解析）
查询速度	慢	快
索引支持	有限	完整（GIN索引）
保留空格	是	否
推荐使用	否	是

-- MySQL的JSON类型
-- CREATE TABLE user_profiles (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     username VARCHAR(50),
--     preferences JSON
-- );
--
-- INSERT INTO user_profiles (username, preferences) VALUES
--     ('alice', '{"theme": "dark", "notifications": true}');
--
-- SELECT
--     username,
--     JSON_EXTRACT(preferences, '$.theme') AS theme
-- FROM user_profiles;

7.3 数组类型（PostgreSQL独有）

-- PostgreSQL：数组类型
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    tags TEXT[],                      -- 文本数组
    ratings INTEGER[],                -- 整数数组
    coordinates POINT[]               -- 点数组
);

-- 插入数组数据
INSERT INTO articles (title, tags, ratings) VALUES
    ('PostgreSQL Tutorial', ARRAY['database', 'postgresql', 'tutorial'], ARRAY[5, 4, 5]),
    ('Advanced SQL', '{"sql", "advanced", "database"}', '{4, 5, 5, 4}');

-- 查询数组
SELECT
    title,
    tags[1] AS first_tag,             -- 数组索引从1开始
    array_length(tags, 1) AS tag_count,
    5 = ANY(ratings) AS has_five_star
FROM articles;

-- 数组操作
SELECT
    tags || ARRAY['new-tag'] AS updated_tags,  -- 追加元素
    array_append(tags, 'another-tag'),
    array_prepend('first-tag', tags),
    array_cat(tags, ARRAY['tag1', 'tag2']);    -- 连接数组

-- 搜索数组
SELECT * FROM articles WHERE 'database' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql'];  -- 包含

-- 创建GIN索引加速数组查询
CREATE INDEX idx_tags ON articles USING GIN(tags);

MySQL没有数组类型，需要使用：

逗号分隔的字符串（不推荐）
JSON数组
关联表（推荐）

7.4 范围类型（PostgreSQL独有）

-- PostgreSQL：范围类型
CREATE TABLE room_bookings (
    id SERIAL PRIMARY KEY,
    room_number INTEGER,
    booking_period DATERANGE,         -- 日期范围
    price_range INT4RANGE,            -- 整数范围
    temperature_range NUMRANGE        -- 数值范围
);

-- 插入范围数据
INSERT INTO room_bookings (room_number, booking_period, price_range) VALUES
    (101, '[2026-03-15, 2026-03-20)', '[100, 150)'),
    (102, '[2026-03-18, 2026-03-25)', '[120, 180)');

-- 范围查询
SELECT * FROM room_bookings
WHERE booking_period @> DATE '2026-03-16';  -- 包含特定日期

SELECT * FROM room_bookings
WHERE booking_period && '[2026-03-16, 2026-03-19)'::daterange;  -- 重叠

-- 范围操作
SELECT
    '[1,10]'::int4range + '[5,15]'::int4range AS union_range,
    '[1,10]'::int4range * '[5,15]'::int4range AS intersect_range,
    '[1,10]'::int4range - '[5,15]'::int4range AS diff_range;

-- 创建GIST索引加速范围查询
CREATE INDEX idx_booking_period ON room_bookings USING GIST (booking_period);

范围类型：

INT4RANGE：整数范围
INT8RANGE：长整数范围
NUMRANGE：数值范围
TSRANGE：时间戳范围
TSTZRANGE：带时区时间戳范围
DATERANGE：日期范围

7.5 几何类型

-- PostgreSQL：几何类型
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position POINT,                   -- 点
    area POLYGON,                     -- 多边形
    route PATH,                       -- 路径
    boundary CIRCLE                   -- 圆
);

-- 插入几何数据
INSERT INTO locations (name, position, boundary) VALUES
    ('City Center', POINT(40.7128, -74.0060), CIRCLE(POINT(40.7128, -74.0060), 10));

-- 几何查询
SELECT
    name,
    position <-> POINT(40.7589, -73.9851) AS distance  -- 计算距离
FROM locations
ORDER BY distance
LIMIT 5;

注意： 复杂的GIS应用建议使用PostGIS扩展。

7.6 网络地址类型

-- PostgreSQL：网络地址类型
CREATE TABLE network_logs (
    id SERIAL PRIMARY KEY,
    client_ip INET,                   -- IP地址
    client_mac MACADDR,               -- MAC地址
    subnet CIDR                       -- 网络地址
);

-- 插入数据
INSERT INTO network_logs (client_ip, client_mac, subnet) VALUES
    ('192.168.1.100', '08:00:2b:01:02:03', '192.168.1.0/24'),
    ('2001:db8::1', '08:00:2b:01:02:04', '2001:db8::/32');

-- 网络查询
SELECT * FROM network_logs WHERE client_ip << '192.168.1.0/24';  -- 包含在子网
SELECT * FROM network_logs WHERE subnet >>= '192.168.0.0/16';    -- 包含子网

7.7 枚举类型

-- PostgreSQL：自定义枚举类型
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending',
    customer_mood mood
);

-- 插入数据
INSERT INTO orders (status, customer_mood) VALUES
    ('processing', 'happy'),
    ('shipped', 'ok');

-- 查询
SELECT * FROM orders WHERE status = 'pending';

-- 修改枚举类型
ALTER TYPE order_status ADD VALUE 'refunded' AFTER 'cancelled';

-- MySQL：使用ENUM类型
-- CREATE TABLE orders (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending'
-- );
--
-- MySQL的ENUM按数字存储，修改不如PostgreSQL灵活

8. 📋 类型转换

-- PostgreSQL：类型转换
SELECT
    '123'::INTEGER AS str_to_int,
    123::TEXT AS int_to_str,
    '2026-03-15'::DATE AS str_to_date,
    NOW()::DATE AS timestamp_to_date,
    CAST('123' AS INTEGER) AS cast_example,
    '{"key": "value"}'::JSONB AS str_to_jsonb;

-- 安全类型转换（转换失败返回NULL）
SELECT
    '123abc'::INTEGER;     -- 错误
-- PostgreSQL 13+可以使用
-- SELECT pg_input_is_valid('123abc', 'INTEGER');  -- false

-- MySQL类型转换
-- SELECT
--     CAST('123' AS SIGNED) AS str_to_int,
--     CAST(123 AS CHAR) AS int_to_str,
--     CAST('2026-03-15' AS DATE) AS str_to_date;

9. 🎯 最佳实践

9.1 1. 选择合适的数值类型

-- 好的做法
CREATE TABLE products (
    id SERIAL PRIMARY KEY,              -- 自增主键
    price NUMERIC(10, 2),               -- 金额用NUMERIC
    quantity SMALLINT,                  -- 数量较小用SMALLINT
    views BIGINT                        -- 大数用BIGINT
);

-- 避免
CREATE TABLE products_bad (
    id INTEGER,                         -- 应该用SERIAL
    price REAL,                         -- 金额不要用浮点数！
    quantity BIGINT,                    -- 浪费空间
    views INTEGER                       -- 可能溢出
);

9.2 2. 字符串类型选择

-- PostgreSQL：TEXT vs VARCHAR
CREATE TABLE articles (
    title VARCHAR(200),                 -- 有明确长度限制的用VARCHAR
    content TEXT,                       -- 无固定长度的用TEXT
    country_code CHAR(2)                -- 固定长度用CHAR
);

-- 性能：在PostgreSQL中，TEXT和VARCHAR(n)性能几乎相同

9.3 3. 时间戳最佳实践

-- 推荐：使用TIMESTAMPTZ（带时区）
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 不推荐：使用TIMESTAMP（无时区）
-- 除非你确定所有时间都在同一时区

9.4 4. JSON vs JSONB

-- 推荐：几乎总是使用JSONB
CREATE TABLE user_data (
    id SERIAL PRIMARY KEY,
    settings JSONB,                     -- 使用JSONB
    raw_data JSON                       -- 仅当需要保留格式时用JSON
);

-- JSONB支持索引
CREATE INDEX idx_settings ON user_data USING GIN (settings);

9.5 5. 使用数组代替多对多表（适用于简单场景）

-- 简单场景：使用数组
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]                         -- 标签数组
);

-- 复杂场景：使用关联表
CREATE TABLE posts_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

10. 📝 练习题

创建一个products表，包含各种数值类型
创建一个blog_posts表，使用TEXT、JSONB和数组类型
创建一个events表，正确使用时间戳和时区
使用UUID作为主键创建表
创建一个包含范围类型的预订系统表

10.1 参考答案

-- 1. 产品表
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    cost NUMERIC(10, 2),
    stock_quantity SMALLINT DEFAULT 0,
    weight REAL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. 博客文章表
CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    content TEXT NOT NULL,
    tags TEXT[],
    metadata JSONB,
    published_at TIMESTAMPTZ
);

-- 3. 事件表
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    starts_at TIMESTAMPTZ NOT NULL,
    ends_at TIMESTAMPTZ NOT NULL,
    timezone VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. UUID主键表
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER NOT NULL,
    token VARCHAR(255) UNIQUE,
    expires_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 5. 预订系统表
CREATE TABLE room_reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    guest_name VARCHAR(100),
    reservation_period TSTZRANGE NOT NULL,
    price_per_night NUMERIC(10, 2),
    EXCLUDE USING GIST (room_id WITH =, reservation_period WITH &&)
);

11. 📚 下一步

学习表的创建与管理
了解约束与索引

关键要点：

PostgreSQL的类型系统比MySQL更丰富
金融计算必须使用NUMERIC
推荐使用TIMESTAMPTZ存储时间
JSONB几乎总是优于JSON
数组和范围类型是PostgreSQL的独特优势

目录