Postgres如何格式化hstore列得到指定格式的updated_column

目标是将hstore类型字段输出为指定的键:值逗号分隔格式，和目标updated_column格式完全对齐，不需要逐字段硬编码判断。

你最初考虑的逐字段CASE WHEN写法维护成本很高，hstore原生提供了键值遍历能力，完全不需要枚举所有字段写判断。

通用无硬编码实现（推荐） #

直接用hstore自带的each()函数展开所有键值对，拼接后聚合即可，适配任意键组合的hstore字段：

SELECT
  原表主键,
  string_agg(format('%s:%s', key, value), ', ' ORDER BY key) AS updated_column
FROM 你的业务表,
     each(hstore_column) AS hstore_kv(key, value)
GROUP BY 原表主键;

• 如果需要固定键的输出顺序，把ORDER BY key替换为自定义排序规则即可，比如要固定按id、name、status的顺序输出，可以写ORDER BY array_position(array['id','name','status']::text[], key)
• 如果需要过滤特定不需要输出的键，在聚合前加WHERE key NOT IN ('不需要输出的字段名')即可

适配json/jsonb类型字段的写法 #

如果你实际操作的是json/jsonb类型列（和你示例里写的json_column对应），把展开函数换成jsonb_each_text()即可，逻辑完全一致：

SELECT
  原表主键,
  string_agg(format('%s:%s', key, value), ', ' ORDER BY key) AS updated_column
FROM 你的业务表,
     jsonb_each_text(json_column) AS json_kv(key, value)
GROUP BY 原表主键;

不推荐CASE写法的原因 #

• 硬编码枚举所有键的维护成本极高，hstore/json里新增/删除键时必须同步修改SQL
• 字段数量多的时候SQL冗余度极高，匹配规则容易写错
• 遇到键缺失、值为空的场景需要额外写大量分支判断，很容易出现格式不符合预期的问题

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