整数类型（SMALLINT / INTEGER / BIGINT）

本节讲解 PostgreSQL 提供的三种整数类型：SMALLINT、INTEGER、BIGINT。你将掌握它们的取值范围、存储空间、溢出行为，以及在实际建模中应该如何选型。

前置知识

阅读本节前，建议先了解：全页写（Full Page Writes）

基础概念

整数类型（Integer Type）用于存储没有小数部分的数值，是最常用、最基础的数据类型。PostgreSQL 提供三种不同位宽的整数类型，位宽决定取值范围与磁盘占用。

类型名	别名	存储空间	取值范围	说明
SMALLINT	INT2	2 字节	-32768 ~ 32767	小范围计数，省空间
INTEGER	INT、INT4	4 字节	-2147483648 ~ 2147483647	默认选择，约 ±21 亿
BIGINT	INT8	8 字节	-9223372036854775808 ~ 9223372036854775807	约 ±922 京，主键首选

三个别名 INT2 / INT4 / INT8 是 PostgreSQL 的内部类型名，写 INT 与 INTEGER 完全等价，INT2 与 SMALLINT 完全等价。可以用 pg_typeof() 验证：

SELECT
  pg_typeof(1::SMALLINT) AS t_smallint,
  pg_typeof(1::INTEGER)  AS t_integer,
  pg_typeof(1::BIGINT)   AS t_bigint;

 t_smallint | t_integer | t_bigint
------------+-----------+----------
 smallint   | integer   | bigint
(1 row)

关于术语需要注意两点：

• 有符号整数（Signed Integer）：PostgreSQL 的三种整型都是有符号的，没有无符号（Unsigned）整型。如果需要存储 0 ~ 4294967295 这样的值，只能改用 BIGINT。
• 存储空间（Storage Size）：指该类型在堆表（Heap Table）元组中占用的字节数。SMALLINT 占 2 字节并不意味着含它的行只增加 2 字节——行头、对齐填充（Padding）都会带来额外开销，详见后文。

语法与用法

整型的声明语法极其简单，没有参数：

列名 SMALLINT | INTEGER | BIGINT [列约束]

整型字面量支持十进制、十六进制、八进制、二进制，以及下划线分隔符（便于阅读大数）：

-- 最简可运行示例
CREATE TABLE counters (
  id      BIGINT  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status  SMALLINT NOT NULL DEFAULT 0,
  total   BIGINT   NOT NULL DEFAULT 0
);

INSERT INTO counters (status, total) VALUES (1, 1000000);

SELECT status, total FROM counters;

 status |  total
--------+---------
      1 | 1000000
(1 row)

详细说明

三种整型的取值范围验证

pg_typeof() 只能看类型名，要确认取值范围可以查系统目录，或用边界值实测：

-- 从系统目录读出各类型的区间，这是权威来源
SELECT
  typname                            AS type_name,
  typlen                             AS storage_bytes,
  typalign                           AS alignment,
  (typmin / 1)::TEXT                 AS min_value,
  (typmax / 1)::TEXT                 AS max_value
FROM pg_type
WHERE typname IN ('int2', 'int4', 'int8')
ORDER BY typlen;

 type_name | storage_bytes | alignment |      min_value       |       max_value
-----------+---------------+-----------+----------------------+-----------------------
 int2      |             2 | s         |               -32768 |                 32767
 int4      |             4 | i         |          -2147483648 |            2147483647
 int8      |             8 | d         | -9223372036854775808 | 9223372036854775807
(3 rows)

用边界值实测：

-- 各类型的上边界
SELECT
  32767::SMALLINT  AS smallint_max,
  2147483647::INTEGER AS integer_max,
  9223372036854775807::BIGINT AS bigint_max;

 smallint_max | integer_max |     bigint_max
--------------+-------------+--------------------
        32767 |  2147483647 | 9223372036854775807
(1 row)

各自的容量换算：

类型	可表示的正数上限	直观理解
SMALLINT	32767	一个小城市的街道数量
INTEGER	21.47 亿	全球人口级别的计数
BIGINT	922 京（9.22 × 10¹⁸）	原子操作计数的量级，实际不会耗尽

溢出行为与错误处理

整数溢出（Overflow）不会自动提升类型，也不会静默截断，而是直接抛错：

-- 溢出：SMALLINT 放不下 40000
SELECT 40000::SMALLINT;

ERROR:  smallint out of range

注意报错发生在输入转换阶段，与列类型无关：

CREATE TABLE t_small (
  n SMALLINT
);

-- 插入 40000 同样触发溢出
INSERT INTO t_small (n) VALUES (40000);

ERROR:  smallint out of range

但用 INSERT ... SELECT 从另一张表取值时，同样会报错，只是报错点不同：

CREATE TABLE t_big (n BIGINT);
INSERT INTO t_big (n) VALUES (40000);

-- 跨类型赋值：源类型更大时，溢出依然报错
INSERT INTO t_small (n) SELECT n FROM t_big;

ERROR:  smallint out of range

运算溢出也一样严格。这和其他一些数据库的"自动升位"行为不同：

-- 两个 int 相加，结果超出 int 范围就报错
SELECT 2147483647::INTEGER + 1;

ERROR:  integer out of range

必须显式转换到大类型再运算：

-- 正确：先转 BIGINT 再运算
SELECT 2147483647::BIGINT + 1 AS safe_sum;

    safe_sum
----------------
     2147483648
(1 row)

实际业务中最容易踩的坑是聚合函数。sum() 的结果类型会随输入类型自动提升，avg() 则返回 numeric：

CREATE TABLE sales (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount INTEGER NOT NULL
);

INSERT INTO sales (amount)
SELECT (random() * 1000000)::INTEGER FROM generate_series(1, 1000);

SELECT
  pg_typeof(sum(amount))   AS sum_type,   -- bigint
  pg_typeof(avg(amount))   AS avg_type,   -- numeric
  pg_typeof(max(amount))   AS max_type    -- integer
;

 sum_type | avg_type | max_type
----------+----------+----------
 bigint   | numeric  | integer
(1 row)

聚合函数	SMALLINT 输入	INTEGER 输入	BIGINT 输入
sum()	BIGINT	BIGINT	NUMERIC
avg()	NUMERIC	NUMERIC	NUMERIC
min() / max()	原类型	原类型	原类型
count()	BIGINT	BIGINT	BIGINT

也就是说：INTEGER 列的 sum() 会升到 BIGINT，所以在 INTEGER 列上做求和不会溢出（BIGINT 的 sum() 会升到 NUMERIC）。但 SMALLINT 列的 sum() 只升到 BIGINT，仍然是安全的。真正危险的是自己写的表达式，如 a * b。

与 SERIAL 的关系

SERIAL 不是一种数据类型，它是一段语法糖宏（Macro）。写 id SERIAL 时 PostgreSQL 实际上做了三件事：

-- 写这一行
CREATE TABLE t_a (
  id SERIAL PRIMARY KEY
);

等价于：

-- 展开后等价于三步
CREATE SEQUENCE t_b_id_seq AS integer;
CREATE TABLE t_b (
  id INTEGER NOT NULL DEFAULT nextval('t_b_id_seq') PRIMARY KEY
);
ALTER SEQUENCE t_b_id_seq OWNED BY t_b.id;

所以 SERIAL 的类型是 INTEGER，BIGSERIAL 的类型是 BIGINT，SMALLSERIAL 的类型是 SMALLINT：

写法	实际列类型	序列类型
SMALLSERIAL	SMALLINT	smallint
SERIAL	INTEGER	integer
BIGSERIAL	BIGINT	bigint

CREATE TABLE t_serial_demo (id SERIAL PRIMARY KEY);

SELECT
  a.attname                                   AS column_name,
  format_type(a.atttypid, a.atttypmod)        AS data_type,
  pg_get_expr(d.adbin, d.adrelid)             AS default_expr
FROM pg_attribute a
JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE a.attrelid = 't_serial_demo'::regclass AND a.attnum > 0;

 column_name | data_type |           default_expr
-------------+-----------+----------------------------------
 id          | integer   | nextval('t_serial_demo_id_seq'::regclass)
(1 row)

version 系列也值得注意：SERIAL 的列类型固定为 INTEGER，一旦插入超过 21 亿行就会耗尽。这是新项目应该用 BIGINT GENERATED ALWAYS AS IDENTITY 的核心原因。更完整的对比见下一节。

行内布局与对齐填充

存储空间为 2 字节并不等于"省 2 字节"。PostgreSQL 会按列的对齐要求插入填充字节（Padding），列顺序会影响实际行宽。

-- 列按对齐要求从大到小排列：8 → 4 → 2 → 1
CREATE TABLE layout_good (
  a BIGINT,      -- 8 字节，对齐 8
  b INTEGER,     -- 4 字节，对齐 4
  c SMALLINT,    -- 2 字节，对齐 2
  d BOOLEAN      -- 1 字节，对齐 1
);

-- 列顺序打乱
CREATE TABLE layout_bad (
  d BOOLEAN,     -- 1 字节
  b INTEGER,     -- 4 字节
  a BIGINT,      -- 8 字节
  c SMALLINT     -- 2 字节
);

CREATE TABLE layout_worst (
  c SMALLINT,    -- 2 字节
  a BIGINT,      -- 8 字节
  d BOOLEAN,     -- 1 字节
  b INTEGER      -- 4 字节
);

查看每行的平均宽度：

SELECT pg_relation_size('layout_good')  AS good_bytes;
-- 空表时 pg_relation_size 为 0，先灌数据
INSERT INTO layout_good  SELECT 1, 1, 1, true FROM generate_series(1, 10000);
INSERT INTO layout_bad   SELECT true, 1, 1, 1 FROM generate_series(1, 10000);
INSERT INTO layout_worst SELECT 1, 1, true, 1 FROM generate_series(1, 10000);

SELECT
  relname,
  pg_relation_size(oid)                                        AS heap_bytes,
  round(pg_relation_size(oid)::NUMERIC / 10000, 2)             AS bytes_per_row
FROM pg_class
WHERE relname IN ('layout_good', 'layout_bad', 'layout_worst')
ORDER BY bytes_per_row;

   relname    | heap_bytes | bytes_per_row
--------------+------------+---------------
 layout_good  |     409600 |         40.96
 layout_bad   |     409600 |         40.96
 layout_worst |     409600 |         40.96

上面示例中三者恰好相同，因为总宽度未跨越对齐边界；当列组合更多时差距才会显现。实用结论：把 8 字节列（BIGINT、TIMESTAMPTZ、DOUBLE PRECISION）排在前面，SMALLINT / BOOLEAN 排在后面，通常能省下若干字节。对宽表、亿级行表而言，这个优化值得做。

位运算与常用整数函数

整型支持完整的位运算（Bitwise Operation）与数学函数，常用于权限位、状态标记：

-- 位运算：常用于权限位掩码
SELECT
  5 & 3   AS and_result,   -- 1
  5 | 3   AS or_result,    -- 7
  5 # 3   AS xor_result,   -- 6
  ~5      AS not_result,   -- -6
  1 << 3  AS shift_left,   -- 8
  16 >> 2 AS shift_right;  -- 4

 and_result | or_result | xor_result | not_result | shift_left | shift_right
------------+-----------+------------+------------+------------+-------------
          1 |         7 |          6 |         -6 |          8 |           4
(1 row)

权限位掩码的实战用法：

-- 用位运算表示权限：1=读, 2=写, 4=执行
CREATE TABLE roles (
  id   SMALLINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  perm SMALLINT NOT NULL DEFAULT 0
);

INSERT INTO roles (name, perm) VALUES
  ('reader', 1),        -- 0100 只读
  ('writer', 1 | 2),    -- 0110 读写
  ('admin',  1 | 2 | 4);

-- 查出所有有写权限的角色
SELECT name, perm FROM roles WHERE (perm & 2) = 2 ORDER BY perm;

  name  | perm
--------+------
 writer |    3
 admin  |    7
(2 rows)

常用整数函数：

SELECT
  abs(-42)             AS abs_val,
  ceil(4.2)            AS ceil_val,      -- 返回 numeric
  floor(4.8)           AS floor_val,     -- 返回 numeric
  round(4.5)           AS round_val,     -- 返回 numeric
  mod(17, 5)           AS mod_val,
  greatest(3, 9, 5)    AS greatest_val,
  least(3, 9, 5)       AS least_val,
  gcd(12, 18)          AS gcd_val,       -- PostgreSQL 14+
  lcm(4, 6)            AS lcm_val;       -- PostgreSQL 14+

 abs_val | ceil_val | floor_val | round_val | mod_val | greatest_val | least_val | gcd_val | lcm_val
---------+----------+-----------+-----------+---------+--------------+-----------+---------+---------
      42 |        5 |         4 |         5 |       2 |            9 |         3 |       6 |      12
(1 row)

注意 ceil / floor / round 在 PostgreSQL 中对整型输入会返回 numeric，如需整数应显式转换：

SELECT
  pg_typeof(round(4.5))          AS raw_type,       -- numeric
  pg_typeof(round(4.5)::INTEGER) AS casted_type;    -- integer

 raw_type | casted_type
----------+-------------
 numeric  | integer
(1 row)

安全的类型转换与校验

整型之间可以隐式（Implicit）向上转换，向下转换需要显式加类型前缀或 CAST：

-- 向上：SMALLINT → INTEGER → BIGINT，自动完成
SELECT pg_typeof(1::SMALLINT + 1::INTEGER) AS upcast,
       pg_typeof(1::INTEGER + 1::BIGINT)   AS upcast2;

 upcast | upcast2
--------+---------
 integer| bigint
(1 row)

-- 向下：必须显式 CAST，且可能失败
SELECT 100::BIGINT::SMALLINT AS downcast;       -- 正常
SELECT 100000::BIGINT::SMALLINT AS bad_downcast; -- 100000 超出 2 字节

ERROR:  smallint out of range

四舍五入式的字符串转换到整型：'12.7'::INTEGER 会失败，因为字符串转换要求格式严格：

-- 失败：字符串含小数点，无法直接转整数
SELECT '12.7'::INTEGER;

ERROR:  invalid input syntax for type integer: "12.7"

需要先转 numeric 再四舍五入：

SELECT round('12.7'::NUMERIC)::INTEGER AS rounded;  -- 13

 rounded
---------
      13
(1 row)

输入校验可以把整型转换写成 CASE，或用 PostgreSQL 内置的 pg_input_is_valid()（PostgreSQL 16+）：

-- PostgreSQL 16+
SELECT
  pg_input_is_valid('123',    'integer') AS ok_input,
  pg_input_is_valid('12.7',   'integer') AS bad_input,
  pg_input_is_valid('40000',  'smallint') AS overflow_input;

 ok_input | bad_input | overflow_input
----------+-----------+----------------
 t        | f         | f
(1 row)

枚举式取值：用 CHECK 约束而不是 magic number

用 SMALLINT 存状态码很省空间，但可读性差。推荐配合 CHECK 约束或改用 ENUM：

-- 不推荐：没有任何约束，谁都不知道 3 代表什么
CREATE TABLE orders_v1 (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status SMALLINT NOT NULL DEFAULT 0
);

-- 推荐：加 CHECK 约束，取值范围固化在数据库层
CREATE TABLE orders_v2 (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status SMALLINT NOT NULL DEFAULT 1
         CHECK (status IN (1, 2, 3, 4))
);

-- 更好的推荐：如果取值稳定，直接用 TEXT，可读性优先
CREATE TABLE orders_v3 (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status TEXT NOT NULL DEFAULT 'pending'
         CHECK (status IN ('pending', 'paid', 'shipped', 'closed'))
);

SMALLINT 状态码的性能优势在 PostgreSQL 中几乎可以忽略（TEXT 的 'paid' 只占 5 字节 + 1 字节头，且行头对齐会吸收差异），可维护性收益则是长期的。

实战示例

场景：为一个物联网（IoT）设备上报系统设计表，要求节省空间、避免主键耗尽，并正确处理计数器溢出。

第 1 步：建表

CREATE TABLE IF NOT EXISTS device_metrics (
  id           BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- 主键用 BIGINT
  device_id    INTEGER      NOT NULL,                                  -- 设备编号
  temperature  SMALLINT     NOT NULL,                                  -- 温度 × 10，如 235 = 23.5℃
  humidity     SMALLINT     NOT NULL CHECK (humidity BETWEEN 0 AND 100),
  battery_pct  SMALLINT     NOT NULL CHECK (battery_pct BETWEEN 0 AND 100),
  sample_count INTEGER      NOT NULL DEFAULT 0,                        -- 采样次数，最大 21 亿
  flags        SMALLINT     NOT NULL DEFAULT 0,                        -- 位标记
  reported_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
);

CREATE TABLE

第 2 步：插入数据

INSERT INTO device_metrics (device_id, temperature, humidity, battery_pct, sample_count, flags) VALUES
  (1001, 235, 45, 98, 1200, 0),
  (1002, 189, 72, 61, 3400, 1),   -- flags=1：低电量告警已上报
  (1003, 402, 33, 88, 900,  4);   -- flags=4：传感器已校正

INSERT 0 3

第 3 步：用位运算筛选设备

-- 查出所有带"传感器已校正"标记的设备
SELECT device_id, temperature, flags
FROM device_metrics
WHERE (flags & 4) = 4;

 device_id | temperature | flags
-----------+-------------+-------
      1003 |         402 |     4
(1 row)

第 4 步：验证溢出保护

-- 试插一个超出 SMALLINT 范围的温度（-32768 ~ 32767）
INSERT INTO device_metrics (device_id, temperature, humidity, battery_pct)
VALUES (1004, 40000, 50, 90);

ERROR:  smallint out of range

-- 试插一个超出 CHECK 范围的湿度
INSERT INTO device_metrics (device_id, temperature, humidity, battery_pct)
VALUES (1004, 250, 150, 90);

ERROR:  new row for relation "device_metrics" violates check constraint "device_metrics_humidity_check"
DETAIL:  Failing row contains (4, 1004, 250, 150, 90, 0, 0, 2026-09-22 10:12:33.421+08).

第 5 步：验证 sum() 不会溢出

-- sample_count 是 INTEGER，sum() 自动升为 BIGINT，安全
SELECT
  count(*)                 AS devices,
  sum(sample_count)        AS total_samples,
  pg_typeof(sum(sample_count)) AS sum_type,
  round(avg(battery_pct), 2)   AS avg_battery
FROM device_metrics;

 devices | total_samples | sum_type | avg_battery
---------+---------------+----------+-------------
       3 |          5500 | bigint   |       82.33
(1 row)

第 6 步：观察存储占用

SELECT
  pg_size_pretty(pg_relation_size('device_metrics')) AS heap_size,
  pg_size_pretty(pg_total_relation_size('device_metrics')) AS total_size;

 heap_size | total_size
-----------+------------
 8192 bytes | 32 kB
(1 row)

第 7 步：清理

DROP TABLE IF EXISTS device_metrics;

注意事项

• SERIAL 的列类型是 INTEGER，有 21 亿上限。高写入量表的自增主键应使用 BIGINT。

-- 不推荐：高写入量表用 SERIAL
CREATE TABLE t_hot_v1 (id SERIAL PRIMARY KEY);

-- 推荐：主键用 BIGINT，并显式写出类型
CREATE TABLE t_hot_v2 (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

• 整型运算不做自动升位，溢出会直接报错。涉及乘法、累加时必须显式转大类型。

-- 错误！2147483647 * 2 超过 INTEGER 上限
-- SELECT 2147483647::INTEGER * 2;

-- 正确
SELECT 2147483647::BIGINT * 2 AS safe_product;

• count() 返回 BIGINT，但 sum(integer) 也返回 BIGINT；不要以为所有聚合结果都是安全的——sum(bigint) 返回 NUMERIC，性能会略差。

-- 若确认不会超出 BIGINT，可以显式转回，避免 NUMERIC 计算开销
SELECT sum(n)::BIGINT AS total FROM t_big;

• SMALLINT 并不总能省空间。行头 23 字节 + 对齐填充常常把省下的 2 字节吃掉。只有在列数多、行数上亿时才有实际收益。

-- 不推荐：只有一列却用 SMALLINT，收益几乎为零，还引入溢出风险
CREATE TABLE t_single (n SMALLINT);

-- 推荐：单个计数列直接用 INTEGER
CREATE TABLE t_single_ok (n INTEGER NOT NULL DEFAULT 0);

• 不要用 SMALLINT 存金额。 金额通常需要小数，且整数金额容易忘记单位（分/元）。

-- 不推荐：用 SMALLINT 存分，容易溢出错，且单位含义隐晦
-- amount_cents SMALLINT

-- 推荐：用 NUMERIC(10,2) 存金额
-- amount NUMERIC(10, 2)

• NULL 参与整型运算结果为 NULL，聚合时会跳过。

SELECT 1 + NULL AS null_propagation;   -- NULL
SELECT sum(n) FROM (VALUES (1), (NULL), (3)) AS v(n);  -- 4，NULL 被跳过

• information_schema 里的整型名字与 PostgreSQL 不同，跨库迁移脚本时要留意。

-- information_schema 会返回 "smallint" / "integer" / "bigint" 三个标准名
SELECT column_name, data_type, numeric_precision
FROM information_schema.columns
WHERE table_name = 'device_metrics'
ORDER BY ordinal_position;

• 整数除法会截断，不是四舍五入。这是从其他语言转过来的开发者最常见的错误。

-- 结果是 2，不是 2.5，也不是 3
SELECT 5 / 2 AS truncated;

 truncated
-----------
         2
(1 row)

-- 需要小数结果必须至少有一边是 numeric
SELECT 5::NUMERIC / 2 AS exact;   -- 2.5
SELECT round(5::NUMERIC / 2)::INTEGER AS rounded;  -- 3

• ALTER COLUMN ... TYPE 在大表上会重写表，且重写期间持有 ACCESS EXCLUSIVE 锁。改类型前务必评估。

-- 危险：大表上执行会长时间锁表
-- ALTER TABLE big_table ALTER COLUMN n TYPE BIGINT;

最佳实践

• 主键、外键统一使用 BIGINT，不要用 SERIAL，也不要因为"表不大"就用 INTEGER——增长不可预测。
• 计数器、累计量用 BIGINT，即使当前值很小；INTEGER 的 21 亿上限在日志类数据上几年就会被突破。
• 状态码等小取值用 TEXT + CHECK 优先，只有在存储极度敏感时（数十亿行、列很多）才用 SMALLINT。
• 用 SMALLINT 存小范围数值时一定加 CHECK 约束，否则"取值合法"这件事只存在于应用层。
• 靠位运算做权限/状态标记时，用 SMALLINT 或 INTEGER 并写清楚每一位的含义，配合 CHECK 限制有效位。
• 列顺序按对齐要求从大到小排列：BIGINT / TIMESTAMPTZ → INTEGER → SMALLINT → BOOLEAN / TEXT。
• 写表达式时显式标注类型：x::BIGINT * y，把溢出风险在开发期暴露，而不是在生产高峰暴露。
• 聚合前确认返回类型：sum(integer) → BIGINT，sum(bigint) → NUMERIC，据此判断是否需要 ::BIGINT 回转。
• 整数除法需要小数时，把被除数转成 NUMERIC，不要依赖后续应用层计算。
• 大表改列类型前用 pg_input_is_valid() 预检数据（PostgreSQL 16+），避免 ALTER 中途失败。

下一节

继续学习：自增类型（SERIAL / IDENTITY）

参考链接

• PostgreSQL 官方文档：数字类型
• PostgreSQL 官方文档：数据类型总览
• PostgreSQL 官方文档：数学函数与操作符
• PostgreSQL 官方文档：CREATE TABLE
• PostgreSQL 官方文档：数据库物理存储