PostgreSQL 数据类型详解

yzqdev2026/7/13大约 4 分钟

PostgreSQL 数据类型详解

PostgreSQL 以丰富的数据类型著称，特别是 JSONB、数组、范围和自定义类型。

数字类型

类型	占用	范围	说明
`SMALLINT`	2 字节	-32768 ~ 32767	小整数
`INTEGER`	4 字节	-21亿 ~ 21亿	常用整数
`BIGINT`	8 字节	±922亿亿	大整数
`DECIMAL(p,s)`	可变	精度 131072 位	精确小数（金额首选）
`NUMERIC(p,s)`	同 DECIMAL	同上	同上
`REAL`	4 字节	6 位精度	浮点数
`DOUBLE`	8 字节	15 位精度	双精度浮点
`SMALLSERIAL`	2 字节	1 ~ 32767	自增小整数
`SERIAL`	4 字节	1 ~ 21亿	自增整数
`BIGSERIAL`	8 字节	1 ~ 922亿亿	自增大整数

-- 推荐使用 GENERATED AS IDENTITY（SQL 标准）
CREATE TABLE users (
    id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name TEXT
);

字符串类型

类型	说明
`CHAR(n)`	定长，不足补空格
`VARCHAR(n)`	变长，有长度限制
`TEXT`	变长，无长度限制（最多 1GB）

title VARCHAR(200) NOT NULL,
summary VARCHAR(500),
content TEXT,
code CHAR(10)

日期时间类型

类型	说明	对应的 Java 类型
`DATE`	日期	`LocalDate`
`TIME`	时间	`LocalTime`
`TIMESTAMP`	日期时间（无时区）	`LocalDateTime`
`TIMESTAMPTZ`	日期时间（带时区）	`Instant`
`INTERVAL`	时间间隔	`Duration` / `Period`

-- TIMESTAMP vs TIMESTAMPTZ
CREATE TABLE events (
    event_name TEXT,
    local_time TIMESTAMP,          -- 不关心时区，存什么就返回什么
    utc_time TIMESTAMPTZ           -- 统一存 UTC，查询时按客户端时区显示
);

-- 时区处理
SET timezone TO 'Asia/Shanghai';
SELECT now();                      -- 当前时区时间
SELECT now() AT TIME ZONE 'UTC';  -- 转 UTC

布尔类型

is_active BOOLEAN DEFAULT TRUE;

INSERT INTO users (is_active) VALUES (TRUE);   -- ✅
INSERT INTO users (is_active) VALUES ('yes');  -- ✅ 可写 'yes'/'no'
INSERT INTO users (is_active) VALUES ('t');    -- ✅ 可写 't'/'f'
INSERT INTO users (is_active) VALUES (1);      -- ✅ 可写 1/0

JSON / JSONB

PostgreSQL 对 JSON 的支持远超其他关系型数据库：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    attributes JSONB       -- 推荐使用 JSONB（支持索引）
);

-- 插入
INSERT INTO products (name, attributes) VALUES
('手机', '{"brand": "华为", "price": 4999, "colors": ["黑", "白"]}'),
('电脑', '{"brand": "Apple", "price": 12999}');

-- 查询 JSON 字段
SELECT name, attributes -> 'brand' AS brand FROM products;        -- 带引号
SELECT name, attributes ->> 'brand' AS brand FROM products;       -- 去引号
SELECT name, attributes #>> '{colors, 0}' AS first_color FROM products;  -- 路径查询

-- 按 JSON 字段过滤
SELECT * FROM products WHERE attributes @> '{"brand": "华为"}';
SELECT * FROM products WHERE (attributes ->> 'price')::numeric > 5000;

-- JSON 修改
UPDATE products SET attributes = jsonb_set(attributes, '{price}', '5999') WHERE id = 1;
UPDATE products SET attributes = attributes || '{"warranty": "1 year"}'::jsonb;
UPDATE products SET attributes = attributes - 'old_field';

-- JSONB 索引
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

数组类型

-- 定义数组列
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]           -- 字符串数组
);

-- 插入
INSERT INTO articles VALUES
(1, 'PostgreSQL 入门', ARRAY['数据库', 'PostgreSQL', '教程']),
(2, 'Java 基础', '{Java, OOP, 入门}');  -- 花括号语法

-- 访问（索引从 1 开始）
SELECT tags[1] FROM articles WHERE id = 1;     -- 数据库
SELECT tags[1:2] FROM articles WHERE id = 1;   -- 切片

-- 查询
SELECT * FROM articles WHERE 'PostgreSQL' = ANY(tags);
SELECT * FROM articles WHERE tags @> ARRAY['数据库'];

-- 数组函数
SELECT array_length(tags, 1) FROM articles;    -- 长度
SELECT unnest(tags) FROM articles;             -- 展开为多行

枚举类型

-- 创建枚举
CREATE TYPE mood AS ENUM ('happy', 'sad', 'angry', 'neutral');

-- 使用
CREATE TABLE person (
    name TEXT,
    current_mood mood
);

INSERT INTO person VALUES ('张三', 'happy');

范围类型

-- 创建范围表
CREATE TABLE reservations (
    room_id INT,
    during TSRANGE       -- 时间范围
);

INSERT INTO reservations VALUES
(101, '[2024-01-15 09:00, 2024-01-15 12:00)');

-- 查询
SELECT * FROM reservations WHERE during @> '2024-01-15 10:00'::timestamp;

-- 范围函数
SELECT lower(during), upper(during) FROM reservations;

UUID 类型

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

-- 使用
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    title TEXT
);

INSERT INTO documents (title) VALUES ('文档1');
-- id 自动生成: f47ac10b-58cc-4372-a567-0e02b2c3d479

网络地址类型

CREATE TABLE servers (
    ip INET,           -- IPv4/IPv6 地址
    network CIDR,      -- CIDR 地址块
    mac MACADDR        -- MAC 地址
);

INSERT INTO servers VALUES
('192.168.1.100', '192.168.1.0/24', '08:00:2b:01:02:03');

SELECT ip <<= '192.168.1.0/24' FROM servers;  -- 判断是否在网段内

全文检索类型

-- tsvector — 文档向量
-- tsquery — 搜索查询

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    fts TSVECTOR GENERATED ALWAYS AS (to_tsvector('simple', title || ' ' || body)) STORED
);

CREATE INDEX idx_fts ON documents USING GIN (fts);

INSERT INTO documents (title, body) VALUES
('PostgreSQL Tutorial', 'PostgreSQL is a powerful database');

SELECT * FROM documents WHERE fts @@ to_tsquery('simple', 'PostgreSQL & database');

类型转换

-- 四种转换方式
SELECT '123'::INT;                         -- :: 操作符
SELECT CAST('123' AS INT);                 -- CAST 函数
SELECT 123::TEXT;                          -- 转字符串
SELECT (price::numeric * 1.1)::numeric(10,2) FROM products;  -- 链式转换

-- 常见转换
SELECT now()::DATE;                        -- timestamp → date
SELECT now()::TIME;                        -- timestamp → time
SELECT '2024-01-15'::TIMESTAMP;            -- string → timestamp

练习

-- 1. 创建一个使用 JSONB 和数组的产品表
-- 2. 插入 3 条数据，包含嵌套 JSON
-- 3. 查询所有品牌为"华为"的产品
-- 4. 创建 UUID 主键的表并插入数据
-- 5. 使用 generate_series 生成过去 7 天的日期序列

PostgreSQL 数据类型详解

详情