跳到主要内容

PostgreSQL 数据类型详解

PostgreSQL 提供了丰富的数据类型，远超大多数关系型数据库。本章将详细介绍各种数据类型及其使用场景。

数值类型​

整数类型​

PostgreSQL 提供三种整数类型：

-- SMALLINT：2 字节，范围 -32768 ~ 32767
CREATE TABLE small_integers (
    id SMALLSERIAL PRIMARY KEY,  -- 使用 SMALLSERIAL 自动生成小整数
    quantity SMALLINT
);

-- INTEGER：4 字节，范围 -2147483648 ~ 2147483647（最常用）
CREATE TABLE normal_integers (
    id SERIAL PRIMARY KEY,       -- 使用 SERIAL 自动生成整数
    user_id INTEGER,
    count INTEGER
);

-- BIGINT：8 字节，范围 -9223372036854775808 ~ 9223372036854775807
CREATE TABLE big_integers (
    id BIGSERIAL PRIMARY KEY,
    big_number BIGINT
);

-- 溢出示例
INSERT INTO small_integers (quantity) VALUES (32767);  -- OK
-- INSERT INTO small_integers (quantity) VALUES (32768);  -- 错误：超出范围

精确数值类型​

NUMERIC（或 DECIMAL）用于需要精确计算的货币金额等场景：

-- NUMERIC(precision, scale)
-- precision：总位数
-- scale：小数位数

CREATE TABLE financial_data (
    amount NUMERIC(10, 2),    -- 最多10位数字，小数2位：9999999.99
    rate NUMERIC(5, 4),       -- 最多5位数字，小数4位：9.9999
    big_number NUMERIC,       -- 无限制精度
    exact_value DECIMAL(15, 5)  -- DECIMAL 是 NUMERIC 的别名
);

-- 插入精确数值
INSERT INTO financial_data (amount, rate, exact_value)
VALUES (12345.67, 0.1234, 123456789.12345);

-- NUMERIC 的特点：精确计算，不会丢失精度
-- 适合：货币金额、科学计算
SELECT 0.1 + 0.2 = 0.3;  -- 返回 FALSE（浮点数）
SELECT NUMERIC '0.1' + NUMERIC '0.2' = NUMERIC '0.3';  -- 返回 TRUE

浮点数类型​

-- REAL：4 字节，单精度
-- DOUBLE PRECISION：8 字节，双精度（默认）
CREATE TABLE measurements (
    temperature REAL,         -- 单精度浮点
    pressure DOUBLE PRECISION, -- 双精度浮点
    value DOUBLE PRECISION
);

-- 特殊浮点值
SELECT 
    'Infinity'::real,         -- 正无穷
    '-Infinity'::real,       -- 负无穷
    'NaN'::real;              -- 非数值

-- 浮点数比较（使用容差）
SELECT 0.1 + 0.2 = 0.3;  -- FALSE
SELECT ABS((0.1 + 0.2) - 0.3) < 0.0001;  -- TRUE

序列类型​

PostgreSQL 使用序列（Sequence）实现自增：

-- SERIAL 等同于：
CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq'),
    name VARCHAR(50)
);

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO CYCLE;

-- 手动操作序列
SELECT nextval('users_id_seq');    -- 获取下一个值
SELECT currval('users_id_seq');    -- 获取当前值
SELECT lastval();                  -- 获取最后一次序列值

-- 设置序列起始值
ALTER SEQUENCE users_id_seq RESTART WITH 1000;

字符串类型​

CHAR 与 VARCHAR​

-- CHAR(n)：固定长度 n，不足部分用空格填充
CHAR(10) 存储 'hello' 实际存储为 'hello     '（10个字符）

-- VARCHAR(n)：可变长度，最大 n
VARCHAR(10) 存储 'hello' 实际存储为 'hello'（5个字符）

-- TEXT：无限制长度
CREATE TABLE articles (
    short_code CHAR(6),      -- 固定6位，如会员编号
    title VARCHAR(200),      -- 标题，最大200字符
    content TEXT             -- 文章内容，无限制
);

-- 性能对比：VARCHAR 和 TEXT 在 PostgreSQL 中性能几乎相同
-- 建议：使用 VARCHAR(n) 做限制，TEXT 存储大文本

字符串函数​

-- 字符串长度
SELECT LENGTH('hello');                  -- 5
SELECT CHAR_LENGTH('你好');              -- 2（字符数）
SELECT OCTET_LENGTH('你好');             -- 6（字节数）

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

-- 字符串截取
SELECT SUBSTRING('hello world', 1, 5);   -- 'hello'（从第1个字符开始，取5个）
SELECT SUBSTRING('hello world', 7);     -- 'world'（从第7个字符开始到末尾）
SELECT LEFT('hello', 3);                 -- 'hel'
SELECT RIGHT('hello', 3);               -- 'llo'

-- 字符串连接
SELECT 'hello' || ' ' || 'world';        -- 'hello world'
SELECT CONCAT('hello', ' ', 'world');   -- 'hello world'
SELECT CONCAT_WS('-', 'a', 'b', 'c');   -- 'a-b-c'

-- 字符串填充
SELECT LPAD('hello', 10, '*');           -- '*****hello'
SELECT RPAD('hello', 10, '*');           -- 'hello*****'
SELECT TRIM('  hello  ');                -- 'hello'

-- 字符串替换
SELECT REPLACE('hello world', 'world', 'postgresql');  -- 'hello postgresql'
SELECT OVERLAY('hello' PLACING 'X' FROM 2 FOR 1);      -- 'hXllo'

-- 字符串分割
SELECT SPLIT_PART('a,b,c,d', ',', 2);  -- 'b'
SELECT REGEXP_SPLIT_TO_ARRAY('hello world', '\s+');    -- '{hello,world}'

日期和时间类型​

日期时间详解​

-- DATE：仅日期
SELECT '2024-01-15'::date;
SELECT CURRENT_DATE;                    -- 当前日期

-- TIME：仅时间
SELECT '14:30:00'::time;
SELECT CURRENT_TIME;                    -- 当前时间（带时区）

-- TIMESTAMP：日期+时间（无时区）
SELECT '2024-01-15 14:30:00'::timestamp;
SELECT CURRENT_TIMESTAMP;               -- 当前时间戳

-- TIMESTAMPTZ：日期+时间+时区
SELECT '2024-01-15 14:30:00+08'::timestamptz;
SELECT NOW();                           -- 当前时间戳（带时区）

-- INTERVAL：时间间隔
SELECT '1 day'::interval;
SELECT '2 hours 30 minutes'::interval;
SELECT INTERVAL '1 week';

日期时间运算​

-- 日期时间加减
SELECT '2024-01-15'::date + INTERVAL '10 days';      -- 2024-01-25
SELECT '2024-01-15'::timestamp - INTERVAL '1 month'; -- 2023-12-15
SELECT '2024-01-15 14:30:00'::timestamp + '2 hours'::interval;  -- 2024-01-15 16:30:00

-- 计算日期间隔
SELECT '2024-01-25'::date - '2024-01-15'::date;      -- 10（天数）
SELECT AGE('2024-01-15', '2024-01-01');              -- 14 days
SELECT AGE('2024-01-15');                            -- 从当前日期的年龄

-- 提取日期部分
SELECT EXTRACT(YEAR FROM '2024-01-15'::date);       -- 2024
SELECT EXTRACT(MONTH FROM '2024-01-15'::date);     -- 1
SELECT EXTRACT(DAY FROM '2024-01-15'::date);       -- 15
SELECT EXTRACT(DOW FROM '2024-01-15'::date);        -- 1（星期日=0，星期一=1）
SELECT EXTRACT(QUARTER FROM '2024-05-15'::date);   -- 2（季度）

-- 日期格式化
SELECT TO_CHAR('2024-01-15'::date, 'YYYY-MM-DD');          -- '2024-01-15'
SELECT TO_CHAR('2024-01-15'::date, 'YYYY年MM月DD日');      -- '2024年01月15日'
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');           -- '2024-01-15 14:30:00'

-- 字符串转日期
SELECT TO_DATE('20240115', 'YYYYMMDD');            -- 2024-01-15
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT '2024-01-15'::date;                         -- 简单转换

布尔类型​

-- 布尔类型可以接受多种值
CREATE TABLE flags (
    is_active BOOLEAN,
    is_verified BOOLEAN DEFAULT FALSE
);

INSERT INTO flags (is_active, is_verified) VALUES
    (TRUE, FALSE),           -- 布尔字面量
    ('true', 'false'),      -- 字符串
    ('yes', 'no'),          -- yes/no
    (1, 0);                 -- 数值

-- 布尔运算
SELECT TRUE AND FALSE;      -- FALSE
SELECT TRUE OR FALSE;       -- TRUE
SELECT NOT TRUE;            -- FALSE

-- 布尔表达式
SELECT * FROM users WHERE is_active = TRUE;
SELECT * FROM users WHERE is_active IS TRUE;       -- 推荐
SELECT * FROM users WHERE is_active IS NOT TRUE;    -- 包括 FALSE 和 NULL

JSON 类型​

PostgreSQL 原生支持 JSON，是构建现代应用的重要特性。

JSON 与 JSONB​

-- JSON：存储原始 JSON 文本
-- JSONB：存储二进制格式，已解析，更适合查询

CREATE TABLE api_requests (
    id SERIAL PRIMARY KEY,
    request_json JSON,      -- 存储原始文本
    response_json JSONB      -- 存储二进制
);

-- 插入 JSON 数据
INSERT INTO api_requests (request_json, response_json)
VALUES 
    ('{"method": "GET", "path": "/users"}', '{"status": 200, "data": []}'),
    ('{"method": "POST", "body": {"name": "test"}}', '{"id": 1}');

JSON 查询​

-- 使用 -> 获取 JSON 对象字段（返回 JSON）
SELECT request_json->'method' FROM api_requests;  -- "GET"

-- 使用 ->> 获取 JSON 对象字段（返回文本）
SELECT request_json->>'method' FROM api_requests;  -- GET

-- 使用 #> 获取嵌套字段
SELECT response_json#>>'{data,0,name}' FROM api_requests;

-- 使用 @> 检查包含
SELECT * FROM api_requests 
WHERE request_json @> '{"method": "GET"}';

-- 使用 ? 检查键是否存在
SELECT * FROM api_requests 
WHERE request_json ? 'method';

-- 使用 ?| 检查任意键存在
SELECT * FROM api_requests 
WHERE request_json ?| array['method', 'body'];

JSON 函数​

-- 获取所有键
SELECT JSON_OBJECT_KEYS(request_json) FROM api_requests;

-- 获取数组元素
SELECT '["a", "b", "c"]'::json->>2;  -- 'c'

-- 展开为记录集
SELECT * FROM JSON_EACH('{"a": 1, "b": 2}');

-- 展开为文本
SELECT * FROM JSON_EACH_TEXT('{"a": 1, "b": 2}');

-- JSON 构建
SELECT JSON_BUILD_OBJECT('name', 'John', 'age', 30);
-- {"name": "John", "age": 30}

SELECT JSON_AGG(column) FROM table;  -- 聚合为 JSON 数组
SELECT JSON_OBJECT_AGG(key, value) FROM table;  -- 聚合为 JSON 对象

数组类型​

PostgreSQL 原生支持数组，无需额外配置。

创建数组列​

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    scores INTEGER[],          -- 整数数组
    tags TEXT[],               -- 文本数组
    matrix INTEGER[][],        -- 二维数组
    dates DATE[]               -- 日期数组
);

-- 插入数组数据
INSERT INTO students (name, scores, tags)
VALUES 
    ('张三', ARRAY[90, 85, 92], ARRAY['good', 'math']),
    ('李四', '{95, 88, 91}', ARRAY['excellent']);

-- 使用花括号语法插入
INSERT INTO students (name, scores)
VALUES ('王五', '{80, 75, 88}');

数组操作​

-- 访问数组元素（下标从 1 开始）
SELECT scores[1] FROM students WHERE name = '张三';  -- 90

-- 数组切片
SELECT scores[1:2] FROM students WHERE name = '张三';  -- {90, 85}

-- 数组长度
SELECT ARRAY_LENGTH(scores, 1) FROM students;  -- 3

-- 数组包含元素
SELECT * FROM students WHERE 90 = ANY(scores);

-- 数组包含
SELECT * FROM students WHERE scores @> ARRAY[90];

-- 数组交集
SELECT * FROM students WHERE scores && ARRAY[90, 85];

-- 数组追加
SELECT scores || ARRAY[100] FROM students;

-- 数组展开
SELECT UNNEST(scores) FROM students;

UUID 类型​

-- 创建 UUID 列
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER,
    token VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- gen_random_uuid() 需要 PostgreSQL 13+
-- 早期版本使用 uuid-ossp 扩展

-- 启用扩展（早期版本）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 使用 uuid-ossp 生成 UUID
CREATE TABLE sessions_old (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id INTEGER
);

-- 插入 UUID
INSERT INTO sessions (user_id) VALUES (1);
-- 自动生成：550e8400-e29b-41d4-a716-446655440000

网络地址类型​

PostgreSQL 专门为网络数据提供了类型：

-- IP 地址类型
CREATE TABLE hosts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    ip INET,                  -- IPv4 或 IPv6
    ip4 IPv4,                 -- 仅 IPv4
    ip6 IPv6,                 -- 仅 IPv6
    mac MACADDR               -- MAC 地址
);

INSERT INTO hosts (name, ip, mac)
VALUES 
    ('server1', '192.168.1.100', '08:00:2b:01:02:03'),
    ('server2', '10.0.0.1'::inet, NULL);

-- 网络操作
SELECT '192.168.1.0/24'::inet >> '192.168.1.100'::inet;  -- TRUE（包含关系）
SELECT '192.168.1.100'::inet << '192.168.1.0/24'::inet;  -- TRUE

-- 提取网络部分
SELECT network('192.168.1.100/24'::inet);  -- 192.168.1.0/24
SELECT host('192.168.1.100'::inet);       -- 192.168.1.100

枚举类型​

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

-- 使用枚举
CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    current_mood mood
);

INSERT INTO people (name, current_mood)
VALUES ('张三', 'happy'), ('李四', 'sad');

-- 枚举操作
SELECT * FROM people WHERE current_mood > 'sad';  -- happy
SELECT 'happy'::mood = 'HAPPY'::mood;  -- FALSE（区分大小写）

位字符串类型​

-- 位字符串
SELECT B'1010';           -- 二进制
SELECT X'FF';             -- 十六进制

-- 位操作
SELECT B'1010' & B'1100';  -- 1000（按位与）
SELECT B'1010' | B'1100';  -- 1110（按位或）
SELECT B'1010' # B'1100'; -- 0110（按位异或）
SELECT ~B'1010';           -- 0101（按位取反）
SELECT B'1010' << 1;      -- 0100（左移）
SELECT B'1010' >> 1;      -- 0101（右移）

域类型​

域（Domain）是自定义类型，可以添加约束：

-- 创建域
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE DOMAIN email_addr AS VARCHAR(255) CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- 使用域
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age positive_int,       -- 必须是正整数
    email email_addr         -- 必须是有效邮箱格式
);

小结​

本章我们详细学习了 PostgreSQL 的各种数据类型：

  1. 数值类型：SMALLINT、INTEGER、BIGINT、NUMERIC、REAL、DOUBLE PRECISION
  2. 字符串类型：CHAR、VARCHAR、TEXT
  3. 日期时间类型：DATE、TIME、TIMESTAMP、TIMESTAMPTZ、INTERVAL
  4. 布尔类型：BOOLEAN
  5. JSON 类型：JSON、JSONB 及相关操作
  6. 数组类型：一维和多维数组
  7. UUID 类型：通用唯一标识符
  8. 网络地址类型：INET、MACADDR
  9. 枚举类型：自定义枚举
  10. 位字符串类型：二进制和十六进制

理解并正确使用这些数据类型是 PostgreSQL 开发的基础。