PostgreSQL HStore 实战：upsert 如何搞定半结构化数据的 Upsert 与键删除

PostgreSQL HStore 实战upsert 如何搞定半结构化数据的 Upsert 与键删除【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsert本文带你用 Ruby 的 upsert 库即 upsert gem玩转 PostgreSQL 的 HStore 扩展对半结构化数据执行存在即更新、不存在即插入的 Upsert 写入并支持按 Key 精确删除 HStore 字段中的键。全程无需手写 PL/pgSQL 存储函数库会在幕后自动创建并复用它。为什么半结构化数据需要 UpsertHStore 是 PostgreSQL 提供的键值对列类型适合存标签、属性、配置这类半结构化数据。但实际业务中同一行往往会被反复写入新增键、更新值、删除键。手写 SQL 时你至少要处理三种情况行不存在 → 插入行存在 → 合并 HStore||操作符而不是整体覆盖键要删除 → HStore 没有置空概念必须用DELETE()函数移除键upsert库把这三件事封装成一行 API并且实测比 find save 等模拟方式快 70%~90%。三步接入 HStore Upsert第 1 步启用 HStore 扩展CREATE EXTENSION IF NOT EXISTS HSTORE; ALTER TABLE pets ADD COLUMN crazy HSTORE;第 2 步让 pg-hstore gem 可用upsert只负责写入序列化交给 pg-hstoregem pg-hstore第 3 步传 Hash 即可upsert Upsert.new connection, :pets upsert.row({ :name Bill }, :crazy { :a 1, :b 2 })就这么简单 ✅ 重复调用同一行时HStore 键会增量合并而非整体覆盖再次写入{ :a 2 }后列里的值变为{a: 2, b: 2}a被更新b保留。HStore 键删除nil 的 4 种姿势 ️upsert对 setter 里 HStore 值的 nil 做了精细区分这是 spec/hstore_spec.rb 中最有含金量的部分你传的值库的实际行为:crazy { :a 1 }合并新增/更新键a其他键保留:crazy { :a nil }只删除键a其他键保留:crazy { :a 1, :b nil }更新a同时删除b:crazy {}空 Hash 无操作NOOP:crazy nil整列置空:crazy { :x nil }, :eager_nullify false关闭提前删键把x nil原样写入其中eager_nullify默认开启Upsert::Row在初始化时就会扫描 setter把所有值为 nil 的键收集到hstore_delete_keys中单独处理见 lib/upsert/row.rb。多个 HStore 列可以同一次 upsert 中各自删键、各自合并upsert.row({ :name Bill }, :crazy { :a nil }, :cool { 13 17 })甚至键名里带双引号这种危险字符也能安全处理库内部会对键做转义。底层揭秘两种执行引擎的自动切换 ⚡upsert在 lib/upsert/merge_function/postgresql.rb 中做了探测自动选择执行路径路径 A原生 UpsertPostgreSQL 9.5 且选择器列有唯一约束生成INSERT ... ON CONFLICT DO UPDATEHStore 列被编译成合并表达式SET crazy CASE WHEN pets.crazy IS NULL THEN CAST($1 AS hstore) ELSE (pets.crazy || CAST($1 AS hstore)) END有删键需求时还会叠加 HStore 的DELETE()函数DELETE(列 || 新值, ARRAY[key1, key2])见 merge_function/postgresql.rb 中的hstore_delete_function与conflict_bind_placeholders。路径 B透明创建的 PL/pgSQL 函数无唯一约束或旧版本库会按先 UPDATE、未命中再 INSERT、遇唯一键冲突重试一次的经典模式CREATE OR REPLACE FUNCTION一个专用函数删键通过额外的text[]参数传入函数内部执行DELETE(列, 数组)。函数命名规则upsert_版本_表_SEL_选择器_SET_字段定义在 lib/upsert/merge_function.rb长名会自动 CRC32 压缩避免超过 62 字符限制。一个贴心细节函数如果被人误删执行时捕获function ... does not exist错误后会自动重建并重试一次merge_function/postgresql.rb。最佳实践与常见坑注意走原生路径要求选择器列上必须是唯一约束unique constraint仅建唯一索引不够不满足时会自动退回 UDF 路径并打一条 WARNING 日志批量写入用Upsert.batch把 N 次 upsert 包进批次可省掉重复的函数检查是三种数据库上最快的姿势HStore 值落库后全是字符串取出来是{ a 1 }而不是{ a 1 }库不做任何类型转换往整型列塞字符串会直接报错——严格是特性不是 bug想清理库生成的所有函数跑一下Upsert.clear_database_functions(connection)即可源码地图 文件职责lib/upsert/merge_function/postgresql.rb原生/函数双路径、HStore 合并与删键 SQL 生成lib/upsert/column_definition/postgresql.rb识别 HStore 列定义COALESCE(...)合并语义lib/upsert/row.rbeager_nullify开关与删键收集spec/hstore_spec.rbHStore upsert、删键、危险键、多列并发测试README.md用法、性能数据、各数据库 MERGE 技巧全文看完你会发现所谓半结构化数据的 Upsert 与键删除本质上就是nil 语义约定 ||合并 DELETE()删键三件套而 upsert 把它们全部藏进了一次row调用里。【免费下载链接】upsertUpsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE.项目地址: https://gitcode.com/gh_mirrors/ups/upsert创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考