如何从键值结果集创建hstore？现有列转hstore有推荐方法吗？

嘿，这两个PostgreSQL里和hstore相关的需求，都有很直接的解决办法，我给你一步步说清楚：

首先得确保你已经启用了hstore扩展，没启用的话先执行：

CREATE EXTENSION IF NOT EXISTS hstore;

假设你的键值结果集是类似(key text, value text)这样的结构（比如子查询、UNION结果），有两种常用方法：

• 方法1：使用hstore_agg聚合函数
  这是最直接的方式，专门用来聚合键值对生成hstore：

  SELECT hstore_agg(key, value) FROM (
      -- 这里替换成你的键值结果集
      SELECT 'name' AS key, 'Alice' AS value
      UNION ALL
      SELECT 'age' AS key, '30' AS value
      UNION ALL
      SELECT 'city' AS key, 'New York' AS value
  ) AS kv_result;
  

  执行后会得到："name"=>"Alice", "age"=>"30", "city"=>"New York"

• 方法2：用array_agg组合生成hstore
  如果你的PostgreSQL版本不支持hstore_agg（比较旧的版本），可以用数组聚合的方式：

  SELECT hstore(array_agg(key), array_agg(value)) FROM (
      -- 同样替换成你的键值结果集
      SELECT 'name' AS key, 'Alice' AS value
      UNION ALL
      SELECT 'age' AS key, '30' AS value
      UNION ALL
      SELECT 'city' AS key, 'New York' AS value
  ) AS kv_result;
  

  效果和上面完全一致。

这个需求的核心是把表中指定列的列名作为hstore的键，列值作为对应的值，有几种灵活的方式：

• 方法1：指定列名和值数组（精准控制）
  如果你只想转换特定的几列，并且要明确键名（就是列名），可以用这种方式：

  SELECT
      id, -- 保留不需要转换的列
      hstore(
          ARRAY['name', 'age', 'city'], -- 要转换的列名数组
          ARRAY[name::text, age::text, city::text] -- 对应列的数值（转成text类型）
      ) AS user_info
  FROM users; -- 替换成你的表名
  

• 方法2：快速排除不需要的列
  如果你想转换除了少数几列之外的所有列，可以先把整行转成hstore，再减去不需要的键：

  SELECT
      id, -- 保留的列
      hstore(users.*) - ARRAY['id'] AS user_info -- 去掉id列对应的键值对
  FROM users;
  

  这种方式不用逐个列名写，非常省事，适合大部分列都需要转换的场景。

• 方法3：借助JSON过渡（兼容复杂类型）
  如果你的列有非text类型，或者想更灵活地构造键值对，可以先转成JSON再转hstore：

  SELECT
      id,
      hstore(
          json_build_object('name', name, 'age', age, 'city', city)::jsonb
      ) AS user_info
  FROM users;
  

  这种方式能处理更多数据类型，还可以自定义键名（不一定用列名）。

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