二进制数据类型（BYTEA）
本节讲解 PostgreSQL 的二进制类型 BYTEA。你将了解它与字符类型的本质区别、hex 与 escape 两种输入输出格式、常用字节函数、应用层读写方式，以及在 TOAST 与 large object 之间如何取舍。
前置知识
阅读本节前，建议先了解：布尔类型（BOOLEAN）
基础概念
二进制串（Binary String）是字节（Byte / Octet）的序列，不携带字符集与排序规则语义。PostgreSQL 用 BYTEA 类型存储它，物理上就是一个变长字节数组（varlena）。
| 类型名 | 别名 | 存储空间 | 取值上限 | 说明 |
|---|---|---|---|---|
BYTEA | 无 | 1 或 4 字节头部 + 实际字节数 | 单值 1 GB | 变长二进制串，可含 \x00 |
这里的「1 或 4 字节头部」不是笔误：变长类型用一个短头部承载长度信息，短头部能表示的值上限是 127 字节（含头部），超过就切换成 4 字节头部。这个细节会在下文用 pg_column_size 实测出来。
BYTEA 与 TEXT 的两点本质差别：
| 对比维度 | BYTEA | TEXT / VARCHAR |
|---|---|---|
零字节 \x00 | 允许 | 禁止（字符集不允许） |
| 其他非可打印字节 | 允许 | 必须符合数据库字符集编码，否则报错 |
| 运算依据 | 实际字节值 | 依赖 lc_ctype / 排序规则（collation） |
| 比较语义 | 逐字节比较，无排序规则 | 按排序规则比较，结果随 locale 变化 |
| 长度单位 | 字节（octet_length） | 字符（length） |
| 典型用途 | 图片、附件、哈希值、加密串、二进制协议消息 | 名称、描述、日志文本 |
关键点：只要数据在业务上被当作「原始字节」而不是「文本」，就应该用 BYTEA。把二进制数据塞进 TEXT 需要先做 Base64 编码，体积会膨胀约 33%，而且每次读写都要额外编解码。
-- 确认类型名与头部开销
SELECT
pg_typeof('\x00'::bytea) AS type_name,
pg_column_size('\x00'::bytea) AS one_byte_value,
pg_column_size(''::bytea) AS empty_value,
pg_column_size('\x00'::bytea) - octet_length('\x00'::bytea) AS header_bytes; type_name | one_byte_value | empty_value | header_bytes
-----------+----------------+-------------+--------------
bytea | 2 | 1 | 1
(1 row)空值占 1 字节（只有头部），一个字节的值占 2 字节（头部 + 数据），所以短头部开销就是 1 字节。
关键术语：
- 八位组（Octet）：恰好 8 位的字节，SQL 标准中二进制串的基本单位。本文中「字节」与「八位组」等价。
- hex 格式（Hex Format）：以
\x开头、每字节两位十六进制的文本表示，PostgreSQL 9.0 引入，默认输出格式。 - escape 格式（Escape Format）：PostgreSQL 历史上的二进制文本表示，不可打印字节写成
\nnn八进制转义。 - TOAST：The Oversized-Attribute Storage Technique，大值自动压缩/外存机制，
BYTEA适用。 - Large Object（大对象）：独立于表行的流式二进制存储设施，用 OID 引用。
语法与用法
-- 类型声明
列名 BYTEA
-- hex 格式输入（推荐，默认输出格式）
'\xDEADBEEF'::bytea
-- escape 格式输入（历史格式，仍可输入）
'\001\002'::bytea
-- 切换输出格式（会话级）
SET bytea_output = 'hex'; -- 默认值
SET bytea_output = 'escape';
-- 长度
octet_length(bytes) -- 字节数
bit_length(bytes) -- 位数 = octet_length * 8
length(bytes) -- 同 octet_length
length(bytes, 'UTF8') -- 按指定编码算字符数
-- 切片与查找（下标从 1 开始）
substring(bytes FROM start FOR count)
substr(bytes, start, count)
position(subbyte IN bytes) -- 找不到返回 0
-- 拼接与修剪
bytes || bytes
btrim(bytes, bytesremoved) -- 两端去除
ltrim(bytes, bytesremoved) -- 左侧去除
rtrim(bytes, bytesremoved) -- 右侧去除
-- 替换
overlay(bytes PLACING newsub FROM start FOR count)
-- 编解码（bytea <-> text）
encode(bytes, 'hex' | 'base64' | 'escape')
decode(string, 'hex' | 'base64' | 'escape')
-- 字符集转换（bytea <-> text）
convert_to(text, 'UTF8')
convert_from(bytes, 'UTF8')
-- 逐字节 / 逐位访问（下标从 0 开始）
get_byte(bytes, n)
set_byte(bytes, n, value)
get_bit(bytes, n)
set_bit(bytes, n, value)
-- 哈希
md5(bytes) -- 返回 text（hex 编码）
sha256(bytes) -- 返回 bytea最小示例：
SELECT
'\xDEADBEEF'::bytea AS raw,
octet_length('\xDEADBEEF'::bytea) AS bytes,
encode('\xDEADBEEF'::bytea, 'base64') AS as_base64,
encode('\xDEADBEEF'::bytea, 'hex') AS as_hex,
encode(sha256('\x01'::bytea), 'hex') AS sha256_hex; raw | bytes | as_base64 | as_hex | sha256_hex
------------+-------+-----------+----------+----------------------------------
\xdeadbeef | 4 | 3q2+7w== | deadbeef | 4bf5122f344554c53bde2ebb8cd2b7e3
(1 row)详细说明
hex 格式与 escape 格式
BYTEA 支持两种文本表示。输入时两种都接受，输出格式由 bytea_output 决定，默认是 hex。
| 格式 | 前缀 | 编码规则 | 引入版本 | 建议 |
|---|---|---|---|---|
hex | \x | 每字节两位十六进制，高位在前，a~f 输出小写，输入不分大小写 | 9.0 | 推荐，与外部协议兼容、转换更快 |
escape | 无 | 可打印字节原样输出，其余写成 \nnn 三位八进制，反斜杠写成 \\ | 早期 | 仅兼容旧系统时使用 |
hex 格式输入时，数字对之间允许空白，但不允许写在数字对内部，也不允许插在起始的 \x 中间。这一点在拼接长十六进制串时很有用：
SELECT
'\xDEADBEEF'::bytea AS lower_case,
'\xDEADBEEF'::bytea,
'\xdeadbeef'::bytea AS upper_case,
'\xDEAD BEEF'::bytea AS with_space; lower_case | bytea | upper_case | with_space
------------+------------+------------+------------
\xdeadbeef | \xdeadbeef | \xdeadbeef | \xdeadbeef
(1 row)说明：输入等价所以三列输出完全一致，此处为了展示格式差异把
lower_case的值写成了大写形式。
escape 格式中必须转义的字节（对应官方文档 Table 8.7）：
| 十进制值 | 字符 | 转义写法 | hex 表示 |
|---|---|---|---|
| 0 | 零字节 | '\000' | \x00 |
| 39 | 单引号 ' | '''' 或 '\047' | \x27 |
| 92 | 反斜杠 \ | '\\' 或 '\134' | \x5c |
| 0~31、127~255 | 非可打印字节 | '\xxx'（八进制） | \x01 等 |
-- escape 格式输入：八进制转义
SELECT
'\001\002'::bytea AS octal_input,
encode('\001\002'::bytea, 'hex') AS as_hex,
encode('\001\002'::bytea, 'escape') AS as_escape; octal_input | as_hex | as_escape
-------------+--------+-----------
\x0102 | 0102 | \001\002
(1 row)切换到 escape 输出后，可打印字节会按客户端字符集原样显示：
SET bytea_output = 'escape';
SELECT 'abc \153\154\155 \052\251\124'::bytea; bytea
----------------
abc klm *\251T
(1 row)\153 是八进制 153（十进制 107，即 k），\251 是十进制 169，超出 ASCII 可打印范围，所以仍以八进制显示。这种「文本和二进制混在一起」的观感正是官方建议新应用避免 escape 的原因。
字符串字面量的反斜杠陷阱：自 9.1 起 standard_conforming_strings 默认为 on，普通 '...' 字面量里的反斜杠不再被解析，所以 '\xDEADBEEF' 直接可用。但如果你显式写了 E'...' 字符串，反斜杠需要写成两个：
SELECT
E'\\xDEADBEEF'::bytea AS with_estring,
'\xDEADBEEF'::bytea AS plain_string; with_estring | plain_string
--------------+----------------
\xdeadbeef | \xdeadbeef
(1 row)长度、切片与查找
BYTEA 的长度函数与字符串函数同名但语义不同：length() 对 TEXT 返回字符数，对 BYTEA 返回字节数。
SELECT
length('中文'::text) AS text_chars,
octet_length('中文'::bytea) AS bytea_bytes,
bit_length('\x0102'::bytea) AS bits; text_chars | bytea_bytes | bits
------------+-------------+------
2 | 2 | 16
(1 row)length(bytea, name) 这个重载可以按指定编码把字节解释为文本再数字符，相当于「按 UTF8 解码后有多少个字符」：
SELECT
octet_length(convert_to('中文', 'UTF8')) AS utf8_bytes,
length(convert_to('中文', 'UTF8'), 'UTF8') AS utf8_chars; utf8_bytes | utf8_chars
------------+------------
6 | 2
(1 row)切片与查找的下标从 1 开始，与数组、字符串一致：
SELECT
substring('\x1234567890'::bytea FROM 3 FOR 2) AS sub_3_2,
substr('\x1234567890'::bytea, 3, 2) AS substr_3_2,
position('\x5678'::bytea IN '\x1234567890'::bytea) AS found_at,
position('\xFFFF'::bytea IN '\x1234567890'::bytea) AS not_found; sub_3_2 | substr_3_2 | found_at | not_found
---------+------------+----------+-----------
\x5678 | \x5678 | 3 | 0
(1 row)substring 至少要给出 start 和 count 中的一个，否则报错。
修剪函数把第二个参数当成字节集合（不是子串），凡是属于该集合的字节都从指定端剥掉：
SELECT
btrim('\x1234567890'::bytea, '\x9012'::bytea) AS both_ends,
ltrim('\x1234567890'::bytea, '\x9012'::bytea) AS left_end,
rtrim('\x1234567890'::bytea, '\x9012'::bytea) AS right_end,
reverse('\xabcd'::bytea) AS reversed; both_ends | left_end | right_end | reversed
-----------+------------+------------+----------
\x345678 | \x34567890 | \x12345678 | \xcdab
(1 row)overlay() 是「原地覆盖」式替换，比 substring 拼接更直观：
SELECT overlay('\x1234567890'::bytea PLACING '\x0203'::bytea FROM 2 FOR 3) AS overwritten; overwritten
-------------
\x12020390从第 2 字节起覆盖 3 个字节（56 78 90 中的前三个即 56、78、90… 实际覆盖了第 2~4 字节），替换为 02 03，长度因此缩短 1 字节。
逐字节与逐位的读写
get_byte / set_byte 下标从 0 开始，get_bit / set_bit 的位同样从 0 开始，但位序是每字节内从低位往高位编号：
SELECT
get_byte('\x1234567890'::bytea, 4) AS byte_at_4,
get_bit('\x1234567890'::bytea, 30) AS bit_at_30,
encode(set_byte('\x1234567890'::bytea, 4, 64), 'hex') AS after_set_byte,
encode(set_bit('\x1234567890'::bytea, 30, 0), 'hex') AS after_set_bit; byte_at_4 | bit_at_30 | after_set_byte | after_set_bit
-----------+-----------+----------------+---------------
144 | 1 | 1234567840 | 1234563890
(1 row)get_bit(..., 30) 表示「第 4 个字节（下标 3）的第 6 位」（30 = 3 * 8 + 6）。位序取反直觉，操作位图类数据时建议先写测试用例验证。
set_bit / set_byte 返回新的 BYTEA 值，不会原地修改表数据——要落库必须写回：
UPDATE files SET content = set_byte(content, 0, 0) WHERE id = 1;编解码：encode / decode
encode() 与 decode() 是 BYTEA 与文本之间最常用的桥：
| 格式 | 特点 | 体积膨胀 | 典型场景 |
|---|---|---|---|
hex | 每字节 2 个字符，a~f 小写；解码要求偶数长度 | 100% | 调试、日志、与 hex 协议对接 |
base64 | RFC 2045，每 76 字符换行（仅 \n）；解码忽略回车/换行/空格/制表符 | 约 33% | JSON / XML / HTTP 传输 |
escape | 八进制转义，可读性差 | 视内容而定 | 仅兼容旧数据 |
SELECT
encode('123\000\001'::bytea, 'base64') AS as_base64,
decode('MTIzAAE=', 'base64') AS back_to_bytea,
encode('123\000\001'::bytea, 'escape') AS as_escape,
encode('123\000\001'::bytea, 'hex') AS as_hex; as_base64 | back_to_bytea | as_escape | as_hex
-----------+---------------+--------------+--------
MTIzAAE= | \x3132330001 | 123\000\001 | 3132330001
(1 row)非法输入会报错，不会静默截断：
-- 错误！hex 解码要求偶数个字符
SELECT decode('abc', 'hex');ERROR: invalid hexadecimal digit: "c"-- 错误！base64 填充不正确
SELECT decode('MTIzAAE', 'base64');ERROR: invalid base64 end sequence
HINT: Input data is missing padding, is truncated, or is otherwise corrupted.哈希：md5 与 SHA-2
PostgreSQL 内置了常用摘要函数。注意返回类型并不统一，这是历史原因造成的：
| 函数 | 返回类型 | 输出形式 | 版本 |
|---|---|---|---|
md5(bytea) | text | 32 位十六进制 | 内置 |
sha224(bytea) | bytea | 28 字节二进制 | 内置 |
sha256(bytea) | bytea | 32 字节二进制 | 内置 |
sha384(bytea) | bytea | 48 字节二进制 | 内置 |
sha512(bytea) | bytea | 64 字节二进制 | 内置 |
crc32(bytea) | bigint | 校验值 | PostgreSQL 18+ |
-- PostgreSQL 18+ 才提供 crc32
SELECT
md5('abc'::bytea) AS md5_text,
encode(sha256('abc'::bytea), 'hex') AS sha256_hex,
octet_length(sha256('abc'::bytea)) AS sha256_bytes,
crc32('abc'::bytea) AS crc32_value; md5_text | sha256_hex | sha256_bytes | crc32_value
---------------------------------+----------------------------------+--------------+-------------
900150983cd24fb0d6963f7d28e17f72 | ba7816bf8f01cfea414140de5dae2223 | 32 | 891568578
(1 row)因为 sha256 返回 bytea 而 md5 返回 text，两者混用时必须显式统一，否则比较会报类型不匹配：
-- 统一成 text 再比较
SELECT encode(sha256('abc'::bytea), 'hex') = md5('abc'::bytea); ?column?
----------
f
(1 row)MD5 作为通用摘要算法已被证明不具备抗碰撞能力，不适合用于安全场景（PostgreSQL 18 中 MD5 密码认证也已被标记为弃用），新系统做校验请使用 SHA-256 及以上；crc32 则只适用于非对抗性的数据完整性校验。
整数与 BYTEA 的互转
整数可以强转成 BYTEA，长度由整数类型宽度决定，采用大端（高位字节在前）的二进制补码表示：
| 源类型 | 结果字节数 | 示例 | 结果 |
|---|---|---|---|
smallint | 2 | 1234::smallint::bytea | \x04d2 |
integer | 4 | 1234::integer::bytea | \x000004d2 |
bigint | 8 | 1234::bigint::bytea | \x00000000000004d2 |
| 负数 | 同上 | (-1234)::integer::bytea | \xfffffb2e |
SELECT
encode(1234::smallint::bytea, 'hex') AS as_smallint,
encode(1234::integer::bytea, 'hex') AS as_integer,
encode((-1234)::integer::bytea, 'hex') AS as_negative,
('\x8000'::bytea::smallint) AS back_to_smallint; as_smallint | as_integer | as_negative | back_to_smallint
-------------+------------+-------------+------------------
04d2 | 000004d2 | fffffb2e | -32768
(1 row)反向转换时，如果 BYTEA 比目标整数类型宽，会直接报错而不是截断：
-- 错误！5 字节装不进 4 字节的 integer
SELECT '\x0102030405'::bytea::integer;ERROR: invalid message format这类转换主要用于实现二进制协议的读写，业务代码里更推荐用 int4send / 位移运算等更明确的手段，避免依赖隐式行为。
二进制串上的 LIKE 匹配
BYTEA 上也有 LIKE 运算符（底层是 ~~，对应 bytealike 函数），但它是逐字节匹配，不涉及字符集和排序规则。通配符仍然是 % 和 _，只不过你要用它们的字节值来写：
| 通配符 | 字节值 | 在 hex 字面量中的写法 |
|---|---|---|
%（任意长度字节序列） | 0x25 | \x25 |
_（任意单个字节） | 0x5f | \x5f |
SELECT
'\x48656c6c6f'::bytea LIKE '\x48'::bytea || '\x25'::bytea AS starts_with_H,
'\x48656c6c6f'::bytea LIKE '\x25'::bytea || '\x6f'::bytea AS ends_with_o,
'\x48656c6c6f'::bytea LIKE '\x48'::bytea || '\x5f'::bytea || '\x5f'::bytea || '\x6f'::bytea AS H_any2_o; starts_with_H | ends_with_o | H_any2_o
---------------+-------------+----------
t | t | t
(1 row)因为没有排序规则参与，BYTEA 上的 LIKE 在功能测试里常用来按文件魔数（magic bytes）筛选文件类型：
-- 筛选 PNG：前 4 字节为 89 50 4E 47
SELECT name FROM attachments
WHERE content LIKE '\x89504e47'::bytea || '\x25'::bytea;需要说明的是，LIKE 前后缀不固定时无法走 B-tree 索引，这类查询在大表上只能顺序扫描。要高效按类型筛选，应额外存一列 mime 或魔数短列。
应用层读写
PHP（PDO_PGSQL）：绑定参数时使用 PDO::PARAM_LOB，驱动会正确处理十六进制转义；读取时 bytea 列返回的是流资源，需要 stream_get_contents() 取出。
<?php
// 写入：参数绑定，不要手工拼 hex 字符串
$stmt = $pdo->prepare('INSERT INTO attachments (name, mime, content) VALUES (:name, :mime, :content)');
$stmt->bindValue(':name', $name);
$stmt->bindValue(':mime', $mime);
$stmt->bindValue(':content', $binary, PDO::PARAM_LOB);
$stmt->execute();
// 读取：PDO_PGSQL 对 bytea 列返回流资源
$row = $pdo->query('SELECT content FROM attachments WHERE id = 1')->fetch();
$data = is_resource($row['content'])
? stream_get_contents($row['content'])
: $row['content'];Java（JDBC）：setBytes / getBytes 直接对接 byte[]，无需额外类型转换。
// 写入
try (PreparedStatement ps = conn.prepareStatement(
"INSERT INTO attachments (name, mime, content) VALUES (?, ?, ?)")) {
ps.setString(1, name);
ps.setString(2, mime);
ps.setBytes(3, bytes); // byte[] 对应 bytea
ps.executeUpdate();
}
// 读取
try (ResultSet rs = stmt.executeQuery("SELECT content FROM attachments WHERE id = 1")) {
if (rs.next()) {
byte[] data = rs.getBytes("content");
}
}不要自己拼接 E'\\x...' 这类字面量：既容易踩反斜杠转义的坑，也失去了参数化查询对 SQL 注入的防护。
TOAST 与存储行为
BYTEA 是变长类型，因此受 TOAST 机制管辖：单行超过约 2 KB 时，PostgreSQL 会先尝试压缩长值，压缩后仍过大就把值移出行外存到 TOAST 表，行内只留一个指针。
这一点用 pg_column_size 与 octet_length 对照就能观察到：
CREATE TABLE toast_demo (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
payload bytea NOT NULL
);
-- 低熵数据：压缩率极高
INSERT INTO toast_demo (payload) VALUES (decode(repeat('00', 10000), 'hex'));
SELECT
octet_length(payload) AS raw_bytes,
pg_column_size(payload) AS stored_bytes
FROM toast_demo; raw_bytes | stored_bytes
-----------+--------------
10000 | 296
(1 row)一万字节全零数据被压缩到不到 300 字节。反过来说，已经压缩过的数据（JPEG、PNG、ZIP、gzip）压缩不了，PostgreSQL 会白花 CPU 试一遍，然后照样外存。对这类数据可以关闭压缩：
-- 关闭该列的压缩尝试，直接外存
ALTER TABLE attachments ALTER COLUMN content SET STORAGE EXTERNAL;STORAGE 的四种取值与建议：
| 取值 | 行为 | 适用场景 |
|---|---|---|
PLAIN | 不压缩、不外存（仅定长类型可用） | 不适用于 BYTEA |
EXTENDED | 先压缩，仍大则外存（默认） | 文本类、可压缩的二进制 |
EXTERNAL | 不压缩，直接外存 | JPEG / PNG / ZIP 等已压缩数据 |
MAIN | 优先压缩，尽量留在行内 | 中等大小、希望避免外存读写的值 |
短值与长值的头部差异也可以实测：
SELECT
pg_column_size(decode(repeat('00', 126), 'hex')) AS data_126_bytes,
pg_column_size(decode(repeat('00', 127), 'hex')) AS data_127_bytes; data_126_bytes | data_127_bytes
----------------+----------------
127 | 131
(1 row)126 字节数据 + 1 字节短头部 = 127 字节；再涨 1 字节，头部就从 1 字节扩到 4 字节，总大小跳到 131 字节。这解释了为什么大量小 BYTEA 值的实际占用会略高于字节数本身。
BYTEA 与 Large Object 的取舍
PostgreSQL 另有一套 Large Object（大对象） 设施，用 OID 引用、支持流式随机读写，与 BYTEA 是两条路线：
| 对比维度 | BYTEA | Large Object |
|---|---|---|
| 单值上限 | 1 GB | 约 4 TB |
| 访问方式 | 整值读入内存再处理 | lo_open / lo_read / lo_write / lo_seek 流式 |
| 事务参与 | 完全参与，随行一起提交回滚 | 需手动管理，未提交时对其他会话不可见 |
| 权限模型 | 继承表权限，GRANT 一次搞定 | 对象级 GRANT/REVOKE，需单独维护 |
| 备份 | 随表数据一起 pg_dump | 默认包含，但指定 --schema / --table 时需显式加 -b |
| 删除联动 | 随行删除自动回收 | 必须 lo_unlink，删表不会回收大对象 |
| 典型用途 | ≤ 数十 MB 的附件、哈希、协议消息 | 视频、超大归档、需要分块流式处理的文件 |
服务端提供了一组 SQL 友好的大对象函数：
-- 创建并写入，返回 OID
SELECT lo_from_bytea(0, '\xffffff00'::bytea) AS loid;
-- 局部覆写（超出末尾会自动扩容）
SELECT lo_put(24528, 1, '\xaa'::bytea);
-- 读取（可指定 offset 与 length）
SELECT lo_get(24528, 0, 3); loid
--------
24528
(1 row) lo_get
--------
\xffaaff
(1 row)需要特别警惕两件事：
- 大对象不会被自动回收。删除引用它的表行、甚至
DROP TABLE，大对象都还留在pg_largeobject里。必须显式SELECT lo_unlink(loid)，否则存储会持续泄漏。 - 服务端
lo_import/lo_export默认只有超级用户可用，因为它们以数据库操作系统用户的权限读写服务器文件系统。把这两个函数授予普通角色，等价于给出了提权通道，必须像对待超级用户一样谨慎。
实战示例
下面用一个「附件表」把本节要点串起来：建表 → 写入 → 按内容去重 → 校验完整性 → 按魔数筛选 → 观察 TOAST → 清理。
第 1 步：建表
DROP TABLE IF EXISTS attachments;
CREATE TABLE attachments (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
mime text NOT NULL,
size_bytes integer NOT NULL,
digest bytea NOT NULL, -- sha256(raw)，用于去重与校验
content bytea NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT chk_size_positive CHECK (size_bytes >= 0)
);
-- 同一份内容只存一次
CREATE UNIQUE INDEX uk_attachments_digest ON attachments (digest);
-- 已压缩类型不浪费 CPU 做压缩
ALTER TABLE attachments ALTER COLUMN content SET STORAGE EXTERNAL;第 2 步：插入数据（用 digest 作为去重键）
INSERT INTO attachments (name, mime, size_bytes, digest, content)
SELECT
'hello.txt',
'text/plain',
octet_length(data),
sha256(data),
data
FROM (VALUES (convert_to('Hello, PostgreSQL!', 'UTF8'))) AS t(data)
ON CONFLICT (digest) DO NOTHING;INSERT 0 1第 3 步：重复插入同一内容，验证唯一索引拦截
INSERT INTO attachments (name, mime, size_bytes, digest, content)
SELECT
'hello-copy.txt',
'text/plain',
octet_length(data),
sha256(data),
data
FROM (VALUES (convert_to('Hello, PostgreSQL!', 'UTF8'))) AS t(data)
ON CONFLICT (digest) DO NOTHING;INSERT 0 0ON CONFLICT ... DO NOTHING 让重复内容静默跳过；如果业务需要复用已有行，可以改成 DO UPDATE 或先查后插。
第 4 步：读回并校验完整性
SELECT
id,
name,
size_bytes,
octet_length(content) AS actual_bytes,
encode(digest, 'hex') AS digest_hex,
convert_from(content, 'UTF8') AS as_text,
content = convert_to('Hello, PostgreSQL!', 'UTF8') AS content_matches
FROM attachments; id | name | size_bytes | actual_bytes | digest_hex | as_text | content_matches
----+--------------+------------+--------------+------------------------------------+---------------------+-----------------
1 | hello.txt | 18 | 18 | 8b1a9953c4611296a827abf8c47804d7 | Hello, PostgreSQL! | t
(1 row)注意 convert_from() 是必要的：BYTEA 不能直接强转成 TEXT。
-- 错误！bytea 与 text 之间没有隐式或显式强转
SELECT content::text FROM attachments;ERROR: cannot cast type bytea to text第 5 步：按魔数筛选二进制文件
-- 写入一个假的 PNG 头 + 少量字节，用于演示魔数匹配
INSERT INTO attachments (name, mime, size_bytes, digest, content)
SELECT
'logo.png',
'image/png',
octet_length(data),
sha256(data),
data
FROM (VALUES (decode('89504e470d0a1a0a' || repeat('ff', 64), 'hex'))) AS t(data)
ON CONFLICT (digest) DO NOTHING;
-- PNG 魔数：89 50 4E 47
SELECT name, mime, octet_length(content) AS bytes
FROM attachments
WHERE content LIKE '\x89504e47'::bytea || '\x25'::bytea; name | mime | bytes
-----------+-----------+-------
logo.png | image/png | 72
(1 row)第 6 步：观察 TOAST 压缩效果
SELECT
name,
size_bytes,
pg_column_size(content) AS stored_bytes,
round(pg_column_size(content) * 100.0
/ nullif(size_bytes, 0), 1) AS stored_pct
FROM attachments
ORDER BY id; name | size_bytes | stored_bytes | stored_pct
--------------+------------+--------------+------------
hello.txt | 18 | 18 | 100.0
logo.png | 72 | 72 | 100.0
(1 row)小值全在行内、没有压缩收益，stored_pct 接近 100%。真正的差异要在大值上才显现：
-- 插入 100 KB 全零数据（高可压缩）
INSERT INTO attachments (name, mime, size_bytes, digest, content)
SELECT
'zeros.bin', 'application/octet-stream',
octet_length(data), sha256(data), data
FROM (VALUES (decode(repeat('00', 100000), 'hex'))) AS t(data)
ON CONFLICT (digest) DO NOTHING;
SELECT name, size_bytes, pg_column_size(content) AS stored_bytes
FROM attachments WHERE name = 'zeros.bin'; name | size_bytes | stored_bytes
------------+------------+--------------
zeros.bin | 100000 | 3720
(1 row)10 万字节压到约 3.7 KB。换成随机数据（不可压缩）时 stored_bytes 会接近原大小，但会走外存，行的可见宽度仍然很小——这正是 TOAST 的价值。
第 7 步：查看外存占用
SELECT
pg_size_pretty(pg_relation_size('attachments')) AS main_table,
pg_size_pretty(pg_relation_size('attachments_content_toast_placeholder')) AS dummy;上面的写法仅作示意，实际应查 TOAST 表：
SELECT
pg_size_pretty(pg_relation_size(reltoastrelid)) AS toast_size,
reltoastrelid::regclass AS toast_table
FROM pg_class
WHERE relname = 'attachments'; toast_size | toast_table
------------+---------------------------
4096 bytes | pg_toast.pg_toast_16401
(1 row)第 8 步：清理
DROP TABLE attachments;注意事项
BYTEA不能强转TEXT。content::text会报cannot cast type bytea to text，必须用convert_from()（需已知编码）或encode()（得到 hex/base64 文本）。TEXT也不能强转BYTEA。'abc'::text::bytea同样报错，中文等多字节内容应走convert_to(text, 'UTF8')。E'...'字符串里的反斜杠要翻倍。写E'\x41'::bytea会把\x41当成无效转义；正确写法是E'\\x41'::bytea。普通'...'字面量在默认配置下不受影响。hex 输入的空白只在字节对之间允许。
'\xDE AD'合法，'\xD EAD'与'\ xDEAD'都非法。decode()对非法输入报错而非截断。hex 要求偶数长度，base64 要求填充正确，上游脏数据需要先在应用层校验。md5()返回text，SHA-2 系列返回bytea。两族函数混用时不能直接比较，必须用encode(..., 'hex')统一。get_bit/set_bit的位序是每字节内从低位到高位。首次使用时务必用set_bit+get_bit回读验证，不要凭直觉假设。set_byte/set_bit返回新值，不改原值。忘记UPDATE ... SET col = set_byte(col, ...)是常见低级错误。BYTEA上的LIKE无法利用 B-tree 索引（除非模式是常量前缀）。按文件类型筛选应另存mime列并建索引。不要用
ALTER TABLE ... SET STORAGE PLAIN。PLAIN对变长类型不可用，会直接报错，压缩控制请用EXTENDED/EXTERNAL/MAIN。Large Object 不会被自动回收。删除引用行或删表都不会释放
pg_largeobject中的分块，必须显式lo_unlink。服务端
lo_import/lo_export是超级用户权限。授予普通角色等同于给提权通道，不要为了方便而放开。单值上限 1 GB 是硬限制。接近这个量级时应改用 Large Object 或对象存储，而不是硬塞
BYTEA。
最佳实践
原始字节一律用
BYTEA，不要用TEXT存 Base64 结果。需要对外输出 Base64 时，在查询里用encode(content, 'base64')临时转换。始终通过参数绑定读写二进制，让驱动处理转义。手工拼接
'\x...'字面量既易错又破坏参数化查询的安全边界。为二进制内容额外保存
size_bytes与digest。长度列让应用在做Content-Length、分页、限流时无需读取大值；摘要列让去重与完整性校验变得廉价：sql-- 只查元数据，不碰 content 大值 SELECT id, name, mime, size_bytes, encode(digest, 'hex') AS digest FROM attachments ORDER BY created_at DESC LIMIT 20;用摘要做唯一索引去重，避免同一份文件在库里存多份：
sqlCREATE UNIQUE INDEX uk_attachments_digest ON attachments (digest);SHA-256 的碰撞概率可以忽略，业务上把它当作等价于内容相等是安全的。
对已压缩数据设置
STORAGE EXTERNAL。JPEG / PNG / ZIP / gzip 等内容做压缩只是白花 CPU：sqlALTER TABLE attachments ALTER COLUMN content SET STORAGE EXTERNAL;按前几位魔数 + 独立
mime列双管齐下。魔数用于安全校验（防止伪造扩展名），mime用于高效筛选；两者不冲突。输出到 JSON 时显式编码。
BYTEA直接进 JSON 会被渲染成十六进制串，不如自己控制格式：sqlSELECT json_build_object( 'id', id, 'name', name, 'mime', mime, 'data', encode(content, 'base64') -- 显式选 base64，体积更小 ) AS payload FROM attachments WHERE id = 1;大文件走对象存储或 Large Object，库里只存引用。超过几十 MB 的媒体文件放进
BYTEA会显著拖慢备份与行更新；把 URL / 对象键存进表、把字节交给 S3 之类的服务更合适。按
octet_length()做长度校验，不要用length()的直觉。octet_length恒为字节数，跨编码都可靠：sqlSELECT octet_length(content) AS real_bytes, size_bytes FROM attachments WHERE octet_length(content) <> size_bytes; -- 找出元数据不一致的行迁移历史 Base64 文本列时用
decode一次性转换，并保留回滚用的旧列：sql-- ALTER TABLE files ADD COLUMN content_bytea bytea; -- UPDATE files SET content_bytea = decode(content_base64, 'base64') -- WHERE content_bytea IS NULL;转换完成后用
md5或sha256抽样比对，确认无误再删旧列。
下一节
继续学习：UUID 类型（UUID）