PostgreSQL更新varying[]类型列：替换元素并去除多余引号

问题本质 #

你看到的双引号并不是数据库存储的多余字符，而是PostgreSQL数组的标准显示规则：当数组元素包含空格、逗号等特殊字符时，PostgreSQL会自动用双引号包裹元素，避免解析数组时出现歧义。比如Introductory Tech作为数组元素，显示时会被格式化为"Introductory Tech"，但实际存储的字符串本身并不带双引号。

验证存储内容 #

你可以通过以下查询确认实际存储的元素：

SELECT unnest("stringIds") FROM "Courses" WHERE "stringIds" @> ARRAY['Introductory Tech']::varchar[];

拆分数组后可以看到，Introductory Tech本身没有双引号。

优化更新语句 #

你的原逻辑可以正常工作，建议做两处优化：一是将array_agg的结果显式转换为和原列一致的varchar[]类型，避免隐式转换可能带来的问题；二是直接用数组包含操作符@>替代JSONB转换，提升查询效率：

UPDATE "Courses"
SET "stringIds" = (
  SELECT array_agg(
    CASE 
      WHEN elem = 'Tech' THEN 'Introductory Tech'::varchar 
      ELSE elem 
    END
  )::varchar[]
  FROM unnest("stringIds") AS elem
)
WHERE "stringIds" @> ARRAY['Tech']::varchar[];

无引号输出的方法 #

如果只是查询时不想看到双引号格式，可通过array_to_string将数组转换为自定义分隔符的字符串：

SELECT array_to_string("stringIds", ', ') FROM "Courses";

输出结果会是Introductory Tech, Sciences, Biology这种无引号的格式。

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