如何在PostgreSQL中结合WITH AS变量使用jsonb_set？

问题场景 #

尝试通过jsonb_set修改teams表中profile字段（jsonb类型）的api_key属性，用CTE定义一个随机十六进制字符串，但执行UPDATE语句时失败，报错提示column "random_string" does not exist。

错误代码 #

WITH random_string AS (SELECT  substr(md5(random()::text), 0, 25)::varchar)

UPDATE 
    teams 
SET 
    profile = jsonb_set(profile, '{api_key}', random_string)
WHERE
    team_id="abc123";

报错信息 #

Postgres query failed, PostgresPlugin query failed to execute: error: column "random_string" does not exist

问题原因 #

1. CTE（WITH子句）定义的是临时结果集而非变量，直接使用random_string会被PostgreSQL当成列名，导致找不到该列。
2. jsonb_set的第三个参数要求是jsonb类型，但生成的随机字符串是varchar类型，类型不匹配。
3. 字符串常量需用单引号包裹，原代码中team_id="abc123"的双引号会被解析为标识符，而非字符串值。

解决方案 #

方案1：正确引用CTE中的值并转换类型 #

给CTE的结果集指定列名，在UPDATE中通过子查询获取该值，并转换为jsonb类型：

WITH random_string AS (
    SELECT substr(md5(random()::text), 0, 25)::varchar AS val
)
UPDATE teams 
SET profile = jsonb_set(profile, '{api_key}', (SELECT to_jsonb(val) FROM random_string))
WHERE team_id = 'abc123';

方案2：直接在jsonb_set中生成随机值（更简洁） #

无需CTE，直接将随机字符串生成逻辑嵌入jsonb_set，并转换为jsonb类型：

UPDATE teams 
SET profile = jsonb_set(profile, '{api_key}', to_jsonb(substr(md5(random()::text), 0, 25)::varchar))
WHERE team_id = 'abc123';

关键要点 #

• 引用CTE的结果时，必须通过子查询或JOIN的方式获取具体列值，不能直接使用CTE名称。
• 确保jsonb_set的第三个参数为jsonb类型，使用to_jsonb()函数完成类型转换。
• PostgreSQL中字符串常量使用单引号，双引号仅用于表、列等标识符的引用。

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