如何通过PostgreSQL的JSONB字段仅更新books表指定字段？

方法一：通用自动更新（推荐） #

利用PostgreSQL的jsonb_populate_record函数，它会自动将JSONB数据中的键映射到表的对应字段，仅更新JSONB中存在的字段，未提及的字段保留原有值。修改后的触发器函数如下：

create or replace function on_book_modifications_insert () 
returns trigger 
language plpgsql 
as $$
begin
  UPDATE books b
  SET b = jsonb_populate_record(b, new.new_data)
  WHERE b.id = new.book_id;

  return new;
end;
$$;

说明：

• jsonb_populate_record(b, new.new_data)会以现有行b为基础，用new_data中的键值对覆盖对应字段，未在new_data中出现的字段保持原值。
• 这种方法无需手动维护每个字段，后续books表新增字段时，触发器无需修改即可适配。

方法二：逐个字段手动控制 #

如果需要更精细的字段控制（比如某些字段不允许通过JSON更新），可以用COALESCE结合JSONB提取操作，只在new_data存在对应键时更新字段：

create or replace function on_book_modifications_insert () 
returns trigger 
language plpgsql 
as $$
begin
  UPDATE books 
  SET name = COALESCE(new.new_data ->> 'name', books.name),
      nb_pages = COALESCE((new.new_data ->> 'nb_pages')::integer, books.nb_pages)
  WHERE id = new.book_id;

  return new;
end;
$$;

说明：

• ->>操作符提取JSONB中的值为文本类型，nb_pages需要显式转为integer类型匹配字段类型。
• COALESCE函数会优先使用new_data中的值，若不存在则保留原字段值，避免被设为NULL。

触发器绑定 #

最后记得将触发器函数绑定到book_modifications表的INSERT事件：

CREATE TRIGGER trigger_book_modifications_insert
AFTER INSERT ON book_modifications
FOR EACH ROW
EXECUTE FUNCTION on_book_modifications_insert();

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