在PostgreSQL中能否为JSON数据内部的字段定义对应数据类型？

PostgreSQL本身不直接为JSON内部字段绑定原生类型，但可以通过以下几种方式实现JSON内部字段的类型校验，效果和定义原生数据类型一致：

方案1：基于CHECK约束做类型校验 #

这是最常用的轻量方案，直接在表定义阶段给jsonb字段加校验规则，匹配你TS接口的类型要求：

CREATE TABLE student_data (
    id SERIAL PRIMARY KEY,
    info JSONB NOT NULL,
    -- 校验percentage是数字类型，name是字符串类型
    CONSTRAINT check_info_types CHECK (
        jsonb_typeof(info -> 'percentage') = 'number'
        AND jsonb_typeof(info -> 'name') = 'string'
    )
);

如果还要加额外的数值范围校验，比如percentage是0-100的浮点数，可以追加规则：

CONSTRAINT check_percentage_range CHECK (
    (info ->> 'percentage')::numeric BETWEEN 0 AND 100
)

方案2：使用自定义组合类型+转换 #

如果希望JSON结构和数据库类型强绑定，可以先定义和TS接口匹配的PG自定义类型，插入时做显式/隐式转换即可：

-- 定义和students接口对应的PG类型
CREATE TYPE student_type AS (
    percentage NUMERIC,
    name TEXT
);
-- 建表时可以直接用该类型
CREATE TABLE student_data (
    id SERIAL PRIMARY KEY,
    student student_type NOT NULL
);
-- 插入JSON时直接转换即可，类型不匹配会直接报错
INSERT INTO student_data (student)
SELECT * FROM jsonb_populate_record(null::student_type, '{"percentage": 95.5, "name": "张三"}'::jsonb);

方案3：生成列映射（适合需要高频查询JSON内部字段的场景） #

如果需要经常查询percentage、name字段，还可以把这两个字段提取为生成列，自动关联JSON内部的值，自带类型约束：

CREATE TABLE student_data (
    id SERIAL PRIMARY KEY,
    info JSONB NOT NULL,
    -- 生成列自动映射JSON内部字段，自带类型校验
    percentage NUMERIC GENERATED ALWAYS AS ((info ->> 'percentage')::numeric) STORED,
    name TEXT GENERATED ALWAYS AS ((info ->> 'name')::text) STORED
);

插入时只需要传入info字段的JSON值即可，生成列会自动同步，类型不匹配会直接拦截插入请求。

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