PostgreSQL插入HSTORE数据教程：键值对格式详解

Eloquent中PostgreSQL Hstore需手动转JSON解析：将"key"=>"value"字符串替换=>为:并包裹{}，再用json_decode转换为对象或数组

直接用字符串字面量插入 hstore，格式必须严格

PostgreSQL 的 hstore 类型不接受任意 JSON 或 Python 字典语法，只认一种固定字符串格式："key1"=>"value1", "key2"=>"value2"。双引号不能省，=> 是唯一合法的键值分隔符，逗号后可有空格但不能换行或用中文标点。

常见错误包括：

• 写成 {"a": "b"}（被当作文本，不是 hstore）
• 漏掉外层双引号：a=>"b"（报错：syntax error at or near "a"）
• 键含空格但没引号：first name=>"John"（必须写成 "first name"=>"John"）

正确示例：

INSERT INTO products (props) VALUES ('"color"=>"red", "size"=>"M", "in_stock"=>"true"');

用 hstore() 函数构造更安全，尤其含变量时

拼接字符串易出错，尤其键或值来自用户输入（含双引号、反斜杠等）。此时优先用内置函数 hstore(text[], text[])，它接受两个同长数组，自动转义：

INSERT INTO products (props) VALUES (hstore(ARRAY['price', 'unit'], ARRAY['29.99', 'kg']));

也支持单键值对简写：hstore('key', 'value')。注意：两个参数都必须是 text 类型，数值需显式转换，例如 hstore('count', 42::text)。

对比风险点：

• 手动拼串：'"count"=>"' || 42 || '"' — 若 42 是 null，结果变成 "count"=>"NULL"（字符串），而非缺失键
• 用 hstore()：传入 NULL 值时该键直接被忽略，行为更符合预期

从 JSON 导入 hstore 需显式转换，别指望自动识别

即使表字段是 hstore，PostgreSQL 也不会把 JSON 字符串自动转成 hstore。以下写法无效：

INSERT INTO products (props) VALUES ('{"a":"b", "c":"d"}'); -- 插入的是 text，不是 hstore

必须用 hstore_to_json() 的逆操作 —— 实际要用 hstore(text, text) 或 hstore(json_each_text())：

INSERT INTO products (props) VALUES (hstore((SELECT hstore(array_agg(key), array_agg(value)) FROM json_each_text('{"a":"b","c":"d"}'::json))));

更实用的写法（PostgreSQL 12+）：

INSERT INTO products (props) VALUES ((SELECT hstore(json_each_text('{"a":"b","c":"d"}'))));

注意：JSON 键名若含非 ASCII 字符或特殊符号，hstore 仍能存，但后续用 -> 操作符取值时需加双引号，例如 props->'"user-id"'。

更新 hstore 字段时别直接赋值，用 || 合并更可控

hstore 支持 || 运算符合并，新键覆盖旧键，其余保留。直接 UPDATE ... SET props = 'new' 会清空所有原有键值对。

典型场景：仅更新部分字段，保留其他：

UPDATE products SET props = props || hstore('updated_at', now()::text) WHERE id = 123;

删除某个键？没有原生 delete 函数，得用 delete(hstore, text)：

UPDATE products SET props = delete(props, 'temp_flag') WHERE id = 123;

容易忽略的细节：

• || 左右操作数都必须是 hstore 类型，混用字符串会报错
• 如果原字段为 NULL，NULL || hstore(...) 结果仍是 NULL，需先用 COALESCE(props, ''::hstore)

hstore 的键名区分大小写，且不支持嵌套结构 —— 这些限制在设计初期就得明确，别等到导出数据时才发现所有 "ID" 和 "id" 被当成不同键。