PostgreSQL数据类型怎么选才高效不踩坑？

kknone
380 阅读17分钟

一、PostgreSQL 数据类型概述

PostgreSQL 提供了丰富的原生数据类型，覆盖数值、字符、日期、几何、网络等多个场景。这些类型的设计遵循 SQL 标准，同时扩展了PostgreSQL特有的功能（如几何类型、JSONB）。理解数据类型是设计合理表结构的基础——选择合适的类型能提升存储效率、避免数据错误、优化查询性能。

二、核心数据类型详解

2.1 数值类型：精准存储数字

数值类型是最常用的类型之一，分为整数、串行、浮点、精确数值四大类，选择时需权衡范围、精度、存储成本。

2.1.1 整数类型：固定范围的整数

PostgreSQL 支持3种整数类型，差异在于存储大小和取值范围：

  • smallint（int2）：2字节，范围 -32768 ~ 32767，适合存储小范围整数（如“性别编码”“评分1-5”）。
  • integer（int4/int）：4字节，范围 -2147483648 ~ 2147483647，最常用（如“用户ID”“商品数量”）。
  • bigint（int8）：8字节，范围 -9223372036854775808 ~ 9223372036854775807，适合超大整数（如“订单量”“雪花ID”）。

示例：创建存储商品库存的表：

-- 库存表：商品ID用integer，库存数量用smallint（假设库存不超过3万）
CREATE TABLE product_stock (
    product_id integer PRIMARY KEY,
    stock smallint NOT NULL CHECK (stock >= 0)  -- 库存不能为负
);

-- 插入数据
INSERT INTO product_stock (product_id, stock) VALUES (1001, 500), (1002, 1200);
2.1.2 串行类型：自动递增的整数

串行类型（smallserial/serial/bigserial）是自动递增的整数，本质是“整数类型 + 序列（Sequence）”的组合，适合作为主键（Primary Key）。

  • smallserial（serial2）：对应smallint，自动递增范围1~32767。
  • serial（serial4）：对应integer，范围1~2147483647（最常用）。
  • bigserial（serial8）：对应bigint，范围1~9223372036854775807。

示例：用serial作为用户表主键：

-- 用户表：user_id自动递增
CREATE TABLE users (
    user_id serial PRIMARY KEY,  -- 等价于：user_id integer PRIMARY KEY DEFAULT nextval('users_user_id_seq')
    username varchar(50) NOT NULL UNIQUE
);

-- 插入数据时无需指定user_id，自动生成
INSERT INTO users (username) VALUES ('alice'), ('bob');

-- 查询结果：user_id会是1、2
SELECT * FROM users;
2.1.3 浮点类型：近似数值

浮点类型（real/double precision）用于存储近似小数，适合不需要精确计算的场景（如科学计算、统计预估）。

  • real（float4）：4字节，精度约6位有效数字。
  • double precision（float8/float）：8字节，精度约15位有效数字。

注意：浮点类型有精度损失，比如0.1无法精确存储，因此财务计算（如金额）绝对不能用浮点类型！

示例：存储商品重量（允许近似）：

CREATE TABLE products (
    product_id integer PRIMARY KEY,
    weight real  -- 重量，单位kg（如1.23kg）
);

INSERT INTO products VALUES (1001, 1.23), (1002, 4.5678);
2.1.4 精确数值类型：无精度损失

numeric（或decimal）用于存储精确小数，适合财务、金融等需要高精度的场景（如金额、税率）。格式为numeric(p, s)：

  • p：总有效位数（精度），范围1~1000。
  • s：小数位数（刻度），范围0~p（默认0）。

示例：存储订单金额（精确到分）：

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    amount numeric(10, 2) NOT NULL  -- 总金额，最多10位数字，2位小数（如99999999.99）
);

INSERT INTO orders (amount) VALUES (123.45), (6789.01);

-- 计算总和（无精度损失）
SELECT SUM(amount) FROM orders;  -- 结果：6912.46

2.2 字符类型：存储文本数据

PostgreSQL 提供3种字符类型，核心区别是长度限制和存储方式：

类型格式描述应用场景
char(n)固定长度长度不足时补空格，超过则报错固定长度文本（身份证号）
varchar(n)可变长度最多存储n个字符，超过则报错可变长度文本（用户名、标题）
text可变长度无长度限制（最多1GB）长文本（文章、评论）

注意：char(n)会自动补空格，查询时需注意（如'abc'::char(5)存储为'abc '），因此非必要不推荐使用。

示例：存储用户信息：

CREATE TABLE user_profile (
    user_id integer PRIMARY KEY,
    id_card char(18) NOT NULL UNIQUE,  -- 身份证号固定18位
    username varchar(50) NOT NULL,     -- 用户名最长50字
    bio text                           -- 个人简介，无长度限制
);

INSERT INTO user_profile VALUES (
    1,
    '110101199001011234',  -- 身份证号
    'alice',                -- 用户名
    '喜欢旅行和读书...'      -- 个人简介
);

2.3 日期/时间类型：处理时间数据

日期/时间类型是业务系统的核心类型之一，PostgreSQL 提供4种核心类型：

2.3.1 核心类型说明
类型格式描述
dateYYYY-MM-DD日期（如2023-12-31）
time [without time zone]HH:MI:SS[.FFF]时间（如14:30:00）
time with time zoneHH:MI:SS[.FFF]+TZ带时区的时间（如14:30:00+08:00）
timestamp [without time zone]YYYY-MM-DD HH:MI:SS[.FFF]日期时间（无时区，如2023-12-31 14:30:00）
timestamp with time zone（timestamptz）YYYY-MM-DD HH:MI:SS[.FFF]+TZ带时区的日期时间（推荐！如2023-12-31 14:30:00+08:00）
interval例如1 day 2 hours时间间隔（如2天3小时）

关键建议：

  • 跨时区应用必须用timestamptz（PostgreSQL会将其转换为UTC存储，查询时自动转换为当前会话时区）。
  • 避免用time with time zone（意义不大，因为时间带时区但无日期，无法处理 daylight saving time）。
2.3.2 示例：存储订单时间
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_time timestamptz NOT NULL,  -- 带时区的订单时间
    delivery_time interval  -- 预计配送时间（如1天2小时）
);

-- 插入北京时区的订单时间（UTC+8）
INSERT INTO orders (order_time, delivery_time) VALUES (
    '2023-12-31 14:30:00+08',  -- 北京时间14:30
    '1 day 2 hours'             -- 预计1天2小时后送达
);

-- 查询订单的预计送达时间（order_time + delivery_time）
SELECT order_id, order_time, order_time + delivery_time AS estimated_delivery FROM orders;
-- 结果：estimated_delivery会是2024-01-01 16:30:00+08

2.4 布尔类型：逻辑值存储

布尔类型（boolean或bool）用于存储真/假值，取值为true、false或NULL（表示未知）。

示例：存储用户激活状态：

CREATE TABLE users (
    user_id serial PRIMARY KEY,
    username varchar(50) NOT NULL,
    is_active boolean NOT NULL DEFAULT false  -- 默认未激活
);

-- 激活用户
UPDATE users SET is_active = true WHERE user_id = 1;

-- 查询激活的用户
SELECT * FROM users WHERE is_active = true;

2.5 二进制类型：存储二进制数据

bytea类型用于存储二进制数据（如图片、文件、压缩包），最大存储1GB。

示例：存储用户头像（需先将图片文件放在PostgreSQL的数据目录或指定位置）：

CREATE TABLE user_avatar (
    user_id integer PRIMARY KEY,
    avatar bytea NOT NULL  -- 头像二进制数据
);

-- 插入头像（假设图片在/var/lib/postgresql/data/avatar.png）
INSERT INTO user_avatar (user_id, avatar) VALUES (
    1,
    pg_read_binary_file('avatar.png')  -- 读取二进制文件内容
);

-- 导出头像（将二进制数据写入文件）
SELECT pg_write_binary_file('avatar_export.png', avatar) FROM user_avatar WHERE user_id = 1;

2.6 几何类型：处理空间数据

PostgreSQL 提供丰富的几何类型，用于存储空间数据（如地图坐标、图形），常见类型：

  • point：点（如(x, y)）。
  • line：无限直线（如{a, b, c}表示ax + by + c = 0）。
  • polygon：多边形（如((0,0), (1,0), (1,1), (0,1))）。

示例：存储店铺位置（经纬度）：

CREATE TABLE shops (
    shop_id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    location point NOT NULL  -- 经纬度（如(116.40, 39.90)表示北京天安门）
);

-- 插入店铺位置
INSERT INTO shops (name, location) VALUES ('星巴克', '(116.40, 39.90)');

-- 查询距离某个点1公里内的店铺（需安装postgis扩展，此处简化用几何距离）
SELECT name FROM shops WHERE distance(location, '(116.41, 39.91)') < 1000;  -- distance返回米

2.7 网络类型：存储网络地址

网络类型用于存储IP地址、MAC地址等网络信息，常见类型：

  • inet：IPv4/IPv6地址（如192.168.1.1、2001:db8::1）。
  • cidr：IPv4/IPv6网络地址（如192.168.1.0/24表示整个网段）。
  • macaddr：MAC地址（如00:11:22:33:44:55）。

示例：存储用户登录IP：

CREATE TABLE user_login (
    login_id serial PRIMARY KEY,
    user_id integer NOT NULL,
    login_ip inet NOT NULL,  -- 用户登录IP
    login_time timestamptz NOT NULL
);

-- 插入登录记录
INSERT INTO user_login (user_id, login_ip, login_time) VALUES (
    1,
    '192.168.1.100',  -- IPv4地址
    '2023-12-31 14:30:00+08'
);

-- 查询某个网段的登录记录（如192.168.1.0/24）
SELECT * FROM user_login WHERE login_ip <<= '192.168.1.0/24';  -- <<= 表示“属于该网段”

2.8 JSON类型：存储半结构化数据

PostgreSQL 支持两种JSON类型：json和jsonb，核心区别是存储方式和查询性能：

类型存储方式特点应用场景
json文本保留原始格式，查询慢存储不常查询的JSON
jsonb二进制解析为树形结构，支持索引，查询快频繁查询、更新的JSON

示例：存储商品属性（半结构化数据）：

CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    attributes jsonb NOT NULL  -- 商品属性（如颜色、尺寸）
);

-- 插入商品（属性为JSON对象）
INSERT INTO products (name, attributes) VALUES (
    'T恤',
    '{"color": "red", "size": "M", "material": "cotton"}'::jsonb
);

-- 查询红色T恤（用->>运算符提取JSON字段值）
SELECT * FROM products WHERE attributes->>'color' = 'red';

-- 创建GIN索引（优化JSON查询）
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

2.9 UUID类型：通用唯一标识符

uuid类型用于存储UUID（Universally Unique Identifier），是128位的唯一标识符，适合分布式系统中的唯一标识（如订单ID、用户ID）。

示例：用UUID作为订单ID：

-- 先安装uuid-ossp扩展（生成UUID）
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE orders (
    order_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),  -- 生成随机UUID
    user_id integer NOT NULL,
    amount numeric(10,2) NOT NULL
);

-- 插入订单（无需指定order_id）
INSERT INTO orders (user_id, amount) VALUES (1, 123.45);

-- 查询订单
SELECT * FROM orders;
-- 结果：order_id会是类似'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'的UUID

三、课后 Quiz：巩固所学

问题1：财务系统中存储金额，应该选择哪种数值类型？为什么？

答案：选择numeric(p, s)（或decimal）。因为numeric是精确数值类型，无精度损失；而浮点类型（real/double precision）会有精度损失，无法保证金额的准确性。

问题2：char(10)、varchar(10)、text的核心区别是什么？

答案：

  • char(10)：固定长度，存储10个字符，不足补空格，超过报错。
  • varchar(10)：可变长度，最多存储10个字符，超过报错。
  • text：可变长度，无字符限制（最多1GB）。

问题3：跨时区应用中，日期时间类型应该选timestamp还是timestamptz？为什么？

答案：选timestamptz（带时区的timestamp）。因为timestamptz会将时间转换为UTC存储，查询时自动转换为当前会话的时区，确保跨时区的时间一致性；而timestamp无时区信息，会导致不同时区的用户看到的时间不一致。

四、常见报错与解决方案

报错1：ERROR: value "32768" is out of range for type smallint

原因：smallint的最大值是32767，插入32768超出范围。
解决：

  • 将字段类型改为integer（最大值2147483647）；
  • 检查应用程序是否生成了过大的值（比如年龄不可能到32768）。

报错2：ERROR: invalid input syntax for type boolean: "1"

原因：试图插入整数1到boolean字段（PostgreSQL的boolean只认true/false或't'/'f'）。
解决：

  • 改为插入true（正确）：INSERT INTO tasks (is_completed) VALUES (true);
  • 或用CAST转换：INSERT INTO tasks (is_completed) VALUES (CAST(1 AS boolean));

报错3：ERROR: column "sku" is of type char(8) but expression is of type text

原因：插入的字符串类型与字段类型不匹配（比如用text插入到char(8)）。
解决：

  • 显式转换类型：INSERT INTO products (sku) VALUES (CAST('ABC12345' AS char(8)));
  • 或确保应用程序输出char(8)类型的字符串。

报错4：ERROR: cannot execute UPDATE in a read-only transaction

（注：此报错与数据类型无关，但常见）
原因：数据库处于只读模式（比如备份时）。
解决：

  • 切换到可写模式：SET default_transaction_read_only = false;

参考链接

www.postgresql.org/docs/17/dat…

余下文章内容请点击跳转至 个人博客页面 或者 扫码关注或者微信搜一搜：编程智域 前端至全栈交流与成长，阅读完整的文章：PostgreSQL数据类型怎么选才高效不踩坑？

往期文章归档
免费好用的热门在线工具
avatar
文章
阅读
粉丝