如何在PostgreSQL中为JSONB列自动生成SHA-256哈希？

完全可以实现自动生成SHA-256哈希，问题出在类型转换的步骤上——PostgreSQL不支持JSONB直接转为BYTEA，但可以通过文本作为中间类型完成转换。

正确的建表语句 #

CREATE TABLE IF NOT EXISTS car (
  metadata JSONB,
  hash TEXT GENERATED ALWAYS AS (encode(sha256(metadata::TEXT::BYTEA), 'hex')) STORED
);

转换逻辑说明 #

• metadata::TEXT：将JSONB类型转换为标准JSON文本字符串，这是PostgreSQL原生支持的转换方式。
• ::BYTEA：把文本字符串转为bytea类型，满足sha256函数的参数类型要求（该函数仅接受bytea输入）。
• encode(..., 'hex')：将二进制哈希结果编码为十六进制文本，方便存储和直观查看。

替代写法（效果一致） #

也可以使用jsonb_to_text函数显式转换JSONB为文本：

CREATE TABLE IF NOT EXISTS car (
  metadata JSONB,
  hash TEXT GENERATED ALWAYS AS (encode(sha256(jsonb_to_text(metadata)::BYTEA), 'hex')) STORED
);

验证测试 #

插入测试数据并查询：

INSERT INTO car (metadata) VALUES ('{"brand": "Toyota", "model": "Camry"}'::JSONB);
SELECT metadata, hash FROM car;

错误原因回顾 #

• 第一次报错：sha256函数没有JSONB类型的重载，必须传入bytea类型参数。
• 第二次报错：PostgreSQL不支持JSONB直接转换为bytea，必须通过文本类型中转。

内容的提问来源于stack exchange，提问作者Michael Lynch