PostgreSQL 数值类型详解

PostgreSQL 提供了丰富的数值数据类型，包括整数、任意精度数字、浮点数和序列类型。本文将详细介绍每种数值类型的特点、使用场景和最佳实践。

数值类型概览

PostgreSQL 的数值类型可以分为四大类：

• 整数类型：smallint、integer、bigint
• 任意精度数字：numeric、decimal
• 浮点类型：real、double precision
• 序列类型：smallserial、serial、bigserial

数值类型对比表

名称	存储大小	描述	范围
smallint	2 字节	小范围整数	-32,768 到 +32,767
integer	4 字节	整数的典型选择	-2,147,483,648 到 +2,147,483,647
bigint	8 字节	大范围整数	-9,223,372,036,854,775,808 到 +9,223,372,036,854,775,807
decimal	可变	用户指定的精度，精确	小数点前最多 131,072 位；小数点后最多 16,383 位
numeric	可变	用户指定的精度，精确	小数点前最多 131,072 位；小数点后最多 16,383 位
real	4 字节	可变精度，不精确	6 位十进制数字精度
double precision	8 字节	可变精度，不精确	15 位十进制数字精度
smallserial	2 字节	小型自增整数	1 到 32,767
serial	4 字节	自增整数	1 到 2,147,483,647
bigserial	8 字节	大型自增整数	1 到 9,223,372,036,854,775,807

整数类型

整数类型特性

整数类型存储没有小数部分的数字，提供不同的存储范围和性能特征。

整数类型选择建议

• integer：大多数情况下的首选，在范围、存储大小和性能之间提供最佳平衡
• smallint：仅在磁盘空间极其有限时使用
• bigint：当 integer 范围不足时使用

实际应用示例

示例 1：用户年龄存储

创建表

-- 用户表，年龄使用 smallint（0-150 足够）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age SMALLINT CHECK (age >= 0 AND age <= 150),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

插入数据

-- 插入用户数据
INSERT INTO users (name, age) VALUES
    ('张三', 25),
    ('李四', 32),
    ('王五', 45);

-- 查询结果
SELECT * FROM users;

查询结果

 id | name | age |         created_at
----+------+-----+----------------------------
  1 | 张三 |  25 | 2024-01-15 10:30:00.123456
  2 | 李四 |  32 | 2024-01-15 10:30:01.234567
  3 | 王五 |  45 | 2024-01-15 10:30:02.345678

示例 2：大数据场景下的 ID 存储

-- 电商订单表，使用 bigint 存储订单号
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount DECIMAL(10, 2),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入大订单号
INSERT INTO orders (order_id, user_id, total_amount) VALUES
    (20240115000001, 1, 299.99),
    (20240115000002, 2, 1599.50);

当预期数据量超过 20 亿条记录时，建议使用 `bigint` 类型作为主键

整数类型错误处理

-- 尝试插入超出范围的值
INSERT INTO users (name, age) VALUES ('测试用户', 32768);
-- 错误：smallint out of range

-- 正确的范围检查
DO $$
BEGIN
    IF 32768 BETWEEN -32768 AND 32767 THEN
        RAISE NOTICE '值在范围内';
    ELSE
        RAISE NOTICE '值超出 smallint 范围';
    END IF;
END $$;

任意精度数字类型（NUMERIC/DECIMAL）

NUMERIC 类型核心概念

NUMERIC 类型提供精确的数值计算，特别适合金融和货币相关的应用。

核心术语：

• 精度（Precision）：整个数字中有效数字的总数
• 标度（Scale）：小数部分中小数位数的计数

NUMERIC 声明语法

完整声明

-- NUMERIC(precision, scale)
CREATE TABLE financial_data (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10, 2),  -- 10位总长度，2位小数
    rate NUMERIC(5, 4),    -- 5位总长度，4位小数
    amount NUMERIC(15, 3)  -- 15位总长度，3位小数
);

仅精度声明

-- NUMERIC(precision) - 标度默认为0
CREATE TABLE inventory (
    item_id SERIAL PRIMARY KEY,
    quantity NUMERIC(8)  -- 整数，最多8位
);

无约束声明

-- NUMERIC - 无约束，任意长度
CREATE TABLE scientific_data (
    measurement_id SERIAL PRIMARY KEY,
    value NUMERIC  -- 任意精度
);

NUMERIC 实际应用案例

案例 1：电商价格系统

-- 创建商品表
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    discount_rate NUMERIC(3, 2) CHECK (discount_rate >= 0 AND discount_rate <= 1),
    weight NUMERIC(8, 3) -- 重量，支持到克
);

-- 插入商品数据
INSERT INTO products (name, price, discount_rate, weight) VALUES
    ('iPhone 15', 5999.99, 0.05, 171.000),
    ('MacBook Pro', 12999.00, 0.10, 1620.500),
    ('AirPods', 1299.50, 0.15, 54.400);

-- 计算折扣后价格
SELECT
    name,
    price AS original_price,
    discount_rate,
    price * (1 - discount_rate) AS final_price,
    ROUND(price * (1 - discount_rate), 2) AS rounded_price
FROM products;

输出结果：

     name      | original_price | discount_rate | final_price | rounded_price
---------------+----------------+---------------+-------------+---------------
 iPhone 15     |        5999.99 |          0.05 |     5699.99 |       5699.99
 MacBook Pro   |       12999.00 |          0.10 |    11699.00 |      11699.00
 AirPods       |        1299.50 |          0.15 |     1104.58 |       1104.58

案例 2：负标度的应用（PostgreSQL 15+）

-- 人口统计表，使用负标度四舍五入到千位
CREATE TABLE population_stats (
    region VARCHAR(100),
    population NUMERIC(5, -3)  -- 四舍五入到千位，范围 -99000 到 99000
);

-- 插入数据
INSERT INTO population_stats VALUES
    ('北京', 21543),    -- 存储为 22000
    ('上海', 24870),    -- 存储为 25000
    ('深圳', 12687);    -- 存储为 13000

SELECT * FROM population_stats;

NUMERIC 特殊值处理

-- 创建测试表
CREATE TABLE special_values (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

-- 插入特殊值
INSERT INTO special_values (value) VALUES
    ('Infinity'),
    ('-Infinity'),
    ('NaN'),
    (123.456);

-- 查询和比较特殊值
SELECT
    value,
    CASE
        WHEN value = 'Infinity'::NUMERIC THEN '正无穷'
        WHEN value = '-Infinity'::NUMERIC THEN '负无穷'
        WHEN value = 'NaN'::NUMERIC THEN '非数字'
        ELSE '常规数值'
    END AS value_type
FROM special_values
ORDER BY value;  -- NaN 被认为最大

> `Infinity` 只能存储在无约束的 `NUMERIC` 列中，因为它在概念上超过了任何有限的精度限制

NUMERIC 舍入行为

-- 演示 NUMERIC 的舍入行为
SELECT
    x,
    ROUND(x::NUMERIC) AS num_round,
    ROUND(x::DOUBLE PRECISION) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) AS x;

输出对比：

  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2  -- NUMERIC远离零舍入
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0  -- NUMERIC远离零舍入
  0.5 |         1 |         0  -- NUMERIC远离零舍入
  1.5 |         2 |         2
  2.5 |         3 |         2  -- NUMERIC远离零舍入
  3.5 |         4 |         4

浮点类型

浮点类型特性

REAL 和 DOUBLE PRECISION 是不精确的、可变精度的数值类型，基于 IEEE 754 标准实现。

浮点类型注意事项

• 不精确性：某些值无法精确存储，可能出现舍入误差
• 比较问题：直接比较两个浮点值可能不会按预期工作
• 精度限制：real 约 6 位精度，double precision 约 15 位精度

浮点类型选择指南

浮点类型实际应用

案例 1：地理信息系统

-- 地理位置表，使用双精度浮点存储经纬度
CREATE TABLE locations (
    location_id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    latitude DOUBLE PRECISION CHECK (latitude >= -90 AND latitude <= 90),
    longitude DOUBLE PRECISION CHECK (longitude >= -180 AND longitude <= 180),
    altitude REAL  -- 海拔高度，单精度足够
);

-- 插入地理数据
INSERT INTO locations (name, latitude, longitude, altitude) VALUES
    ('天安门广场', 39.9042, 116.4074, 44.0),
    ('上海东方明珠', 31.2397, 121.4990, 468.0),
    ('深圳平安大厦', 22.5431, 114.0579, 599.0);

-- 计算两点间距离（简化的平面距离）
SELECT
    l1.name AS from_location,
    l2.name AS to_location,
    SQRT(POW(l1.latitude - l2.latitude, 2) + POW(l1.longitude - l2.longitude, 2)) AS distance_degree
FROM locations l1
CROSS JOIN locations l2
WHERE l1.location_id < l2.location_id;

案例 2：科学数据分析

-- 实验数据表
CREATE TABLE experiment_data (
    experiment_id SERIAL PRIMARY KEY,
    temperature REAL,           -- 温度（摄氏度）
    pressure DOUBLE PRECISION, -- 压力（帕斯卡）
    measurement_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入实验数据
INSERT INTO experiment_data (temperature, pressure) VALUES
    (23.5, 101325.0),
    (25.2, 101400.5),
    (22.8, 101250.3);

-- 统计分析
SELECT
    AVG(temperature) AS avg_temp,
    STDDEV(temperature) AS temp_stddev,
    AVG(pressure) AS avg_pressure,
    STDDEV(pressure) AS pressure_stddev
FROM experiment_data;

浮点精度控制

PostgreSQL 提供了 extra_float_digits 参数来控制浮点数的输出精度：

-- 查看当前精度设置
SHOW extra_float_digits;

-- 设置为最短精确格式
SET extra_float_digits = 1;

-- 演示不同精度输出
SELECT
    1.0/3.0 AS division_result,
    PI() AS pi_value,
    EXP(1) AS e_value;

-- 恢复默认设置
RESET extra_float_digits;

浮点特殊值处理

-- 创建包含特殊值的测试数据
CREATE TABLE float_special_values (
    id SERIAL PRIMARY KEY,
    value DOUBLE PRECISION
);

-- 插入特殊值
INSERT INTO float_special_values (value) VALUES
    ('Infinity'),
    ('-Infinity'),
    ('NaN'),
    (0.0),
    (1.0);

-- 特殊值检测和处理
SELECT
    value,
    CASE
        WHEN value = 'Infinity'::DOUBLE PRECISION THEN '正无穷'
        WHEN value = '-Infinity'::DOUBLE PRECISION THEN '负无穷'
        WHEN value = 'NaN'::DOUBLE PRECISION THEN '非数字'
        WHEN value = 0.0 THEN '零值'
        ELSE '正常值'
    END AS classification
FROM float_special_values
ORDER BY value;  -- NaN 排在最后（最大）

序列类型（自增类型）

序列类型工作原理

序列类型不是真正的数据类型，而是创建自增列的便捷表示法。

序列类型等价转换

当你创建一个 SERIAL 列时，PostgreSQL 实际执行的操作：

SERIAL 声明

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

等价的完整声明

-- PostgreSQL 实际执行的等价操作
CREATE SEQUENCE users_id_seq AS INTEGER;

CREATE TABLE users (
    id INTEGER NOT NULL DEFAULT nextval('users_id_seq'),
    username VARCHAR(50)
);

ALTER SEQUENCE users_id_seq OWNED BY users.id;

-- 通常还会添加主键约束
ALTER TABLE users ADD PRIMARY KEY (id);

序列类型实际应用

案例 1：用户管理系统

-- 创建用户表
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入用户（不指定 user_id）
INSERT INTO users (username, email) VALUES
    ('alice', '[email protected]'),
    ('bob', '[email protected]'),
    ('charlie', '[email protected]');

-- 查看自动生成的 ID
SELECT user_id, username, email FROM users;

输出：

 user_id | username |        email
---------+----------+---------------------
       1 | alice    | [email protected]
       2 | bob      | [email protected]
       3 | charlie  | [email protected]

案例 2：订单系统中的序列间隙

-- 创建订单表
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total_amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending'
);

-- 模拟事务回滚导致的序列间隙
BEGIN;
    INSERT INTO orders (customer_id, total_amount) VALUES (1, 100.00);
    -- 假设这里发生错误
ROLLBACK;

-- 再次插入
INSERT INTO orders (customer_id, total_amount) VALUES (1, 150.00);

-- 查看结果 - 注意 order_id 可能不连续
SELECT * FROM orders;

序列值一旦分配就被"消耗"，即使事务回滚也不会回收，这会导致 ID 序列中出现间隙

序列操作和管理

查看序列信息：

-- 查看表的序列信息
SELECT
    schemaname,
    sequencename,
    last_value,
    start_value,
    increment_by,
    max_value,
    min_value,
    cache_size,
    is_cycled
FROM pg_sequences
WHERE sequencename LIKE '%users%';

手动操作序列：

-- 获取下一个序列值（不推荐在应用中直接使用）
SELECT nextval('users_user_id_seq');

-- 查看当前序列值
SELECT currval('users_user_id_seq');

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

-- 插入记录验证
INSERT INTO users (username, email) VALUES ('david', '[email protected]');
SELECT user_id, username FROM users WHERE username = 'david';

序列类型选择建议：

-- 小型应用，预期记录数 < 32K
CREATE TABLE small_items (
    id SMALLSERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 常规应用，预期记录数 < 2B
CREATE TABLE regular_items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- 大型应用，预期记录数 > 2B
CREATE TABLE large_items (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100)
);

数值类型性能对比

存储空间效率

-- 创建测试表比较存储空间
CREATE TABLE number_comparison (
    id SERIAL PRIMARY KEY,
    small_int SMALLINT,
    regular_int INTEGER,
    big_int BIGINT,
    num_fixed NUMERIC(10,2),
    num_variable NUMERIC,
    real_val REAL,
    double_val DOUBLE PRECISION
);

-- 插入测试数据
INSERT INTO number_comparison
SELECT
    generate_series(1, 10000),
    1000,
    100000,
    1000000000,
    12345.67,
    123456789.123456789,
    123.456,
    123456.789012345
FROM generate_series(1, 10000);

-- 查看表大小
SELECT
    pg_size_pretty(pg_total_relation_size('number_comparison')) AS total_size,
    pg_size_pretty(pg_relation_size('number_comparison')) AS table_size;

计算性能测试

-- 性能测试：不同数值类型的计算速度
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(regular_int), SUM(regular_int)
FROM number_comparison;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(num_fixed), SUM(num_fixed)
FROM number_comparison;

EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*), AVG(double_val), SUM(double_val)
FROM number_comparison;

最佳实践总结

数值类型选择决策树

推荐使用场景

整数类型使用场景

• ID/主键：使用 SERIAL 或 BIGSERIAL
• 年龄/评分：使用 SMALLINT
• 数量/计数：使用 INTEGER
• 大数据量 ID：使用 BIGINT

NUMERIC 使用场景

• 货币金额：始终使用 NUMERIC(precision, 2)
• 百分比/比率：使用 NUMERIC(5, 4)
• 科学计算：需要精确结果时使用 NUMERIC

浮点类型使用场景

• 地理坐标：使用 DOUBLE PRECISION
• 科学测量：使用 REAL 或 DOUBLE PRECISION
• 避免用于货币计算

常见陷阱避免

1. 浮点数精度问题

-- 错误：直接比较浮点数
SELECT * FROM products WHERE price = 19.99;

-- 正确：使用范围比较
SELECT * FROM products WHERE ABS(price - 19.99) < 0.001;

-- 更好：使用 NUMERIC
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10, 2);

2. 序列类型误用

-- 错误：手动指定 SERIAL 值
INSERT INTO users (user_id, username) VALUES (999, 'admin');

-- 正确：让序列自动生成
INSERT INTO users (username) VALUES ('admin');

-- 如果必须指定值，更新序列
SELECT setval('users_user_id_seq', (SELECT MAX(user_id) FROM users));

3. NUMERIC 精度设置不当

-- 可能导致数据截断
CREATE TABLE prices (
    item_id SERIAL,
    price NUMERIC(5, 2)  -- 只能存储 -999.99 到 999.99
);

-- 更合适的设置
CREATE TABLE prices (
    item_id SERIAL,
    price NUMERIC(10, 2)  -- 可存储 -99999999.99 到 99999999.99
);

通过本文的详细介绍，你应该能够根据具体的业务需求选择合适的 PostgreSQL 数值类型，并避免常见的使用陷阱。记住，数值类型的选择不仅影响存储效率，还会影响查询性能和数据准确性。