PostgreSQL：如何基于JSONB列的键创建生成存储列lookup？

问题分析 #

你遇到的错误ERROR: set-returning functions are not allowed in column generation expressions，是因为jsonb_object_keys是返回集合（多行结果）的函数，而生成存储列的表达式必须返回单个标量值，所以无法直接使用。

解决方案 #

需要先将jsonb_object_keys返回的键集合转换为数组，再通过array_to_string函数将数组元素用空格拼接成单个字符串。执行以下SQL语句即可：

ALTER TABLE "table_example"
ADD COLUMN lookup TEXT GENERATED ALWAYS AS (
  array_to_string(
    array(SELECT jsonb_object_keys("emailAddress")),
    ' '
  )
) STORED;

关键说明 #

1. array(SELECT jsonb_object_keys("emailAddress"))：把JSONB列的所有键转换为字符串数组
2. array_to_string(..., ' ')：将数组中的元素用空格连接成完整字符串
3. 该生成列会随emailAddress列的内容更新自动同步，满足你的需求

注意：确保SQL中的列名"emailAddress"和你的实际表列名一致，避免拼写错误。

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