PostgreSQL中如何为含name/date/price的表实现customField自定义字段

下面是两种适配需求的主流实现方案，可根据业务场景选择：

方案1：JSONB 类型存储（最推荐） #

该方案完全适配「自定义标题+动态键值对」的需求，兼顾灵活性和查询效率，是绝大多数业务场景的首选。

建表语句 #

CREATE TABLE 你的表名 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    custom_field JSONB NOT NULL
);

存储格式示例 #

对应你提到的House类商品，存储的JSON结构如下：

{
  "title": "House Fields",
  "fields": {
    "Type": "Apartment",
    "Rooms": 5,
    "Area": 120.5
  }
}

核心优势 #

• 结构完全灵活，不需要提前定义自定义字段的key，可随时新增任意类型的属性
• JSONB支持GIN索引，自定义字段查询效率很高，示例查询语句：

  -- 查询所有户型为Apartment的商品
  SELECT * FROM 你的表名 
  WHERE custom_field -> 'fields' ->> 'Type' = 'Apartment';
  

  加索引后查询性能可接近普通字段：

  CREATE INDEX idx_custom_fields ON 你的表名 USING GIN ((custom_field -> 'fields'));
  

方案2：拆分关联表（适合强约束场景） #

如果你的自定义字段需要做全局统计、强校验，可拆分为两张关联表实现：

主表（存储核心字段） #

CREATE TABLE goods (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    price NUMERIC(10,2) NOT NULL
);

自定义字段关联表 #

CREATE TABLE goods_custom_fields (
    id SERIAL PRIMARY KEY,
    goods_id INT NOT NULL REFERENCES goods(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    field_key VARCHAR(255) NOT NULL,
    field_value TEXT NOT NULL,
    UNIQUE(goods_id, field_key)
);

优劣势说明 #

• 优势：数据结构更规范，支持单独对自定义字段做聚合统计，不需要解析JSON
• 劣势：查询需要关联表，写入时需要批量操作多条数据，动态扩展属性的成本远高于JSONB方案

选型建议 #

• 普通业务场景直接选JSONB方案即可，完全满足你描述的需求
• 只有当自定义字段需要做全局统一管控、高频统计分析时，再考虑关联表方案

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