将字符串转换为数组 - PostgreSQL

Question

我有一张表里有一个存储用逗号分隔的姓名的列，例如："Mel's Hou Rest, Mel's Lad Rest"。我需要将这个字符串转换为用逗号分隔的数组。

我需要的查询语句是：

SELECT home_location, subs_state FROM cust
WHERE (home_location = ANY('{"Mel''s Hou Rest", Mel''s Lad Rest"}')) AND subs_state = 'active'

我尝试了这个，但是一直出现错误：

WHERE (home_location = ANY(string_to_array("Mel's Hou Rest, Mel's Lad Rest", ',')::text[])

有没有办法在我不必将数据库从“text”更改为“array”的情况下完成这个任务？

- MCM13

---

保存逗号分隔字符串的列是哪一列？你想通过查询实现什么目标？ - GMB

Answer 1

SQL使用单引号表示字符串字面值。你的字符串"Mel's Hou Rest, Mel's Lad Rest"有双引号，这会让Postgres将其解释为带引号的标识符。你可以使用两个单引号来在字符串中包含一个单引号。

SELECT * FROM cust WHERE home_location = ANY(string_to_array("Mel's Hou Rest, Mel's Lad Rest", ','))
-- ERROR: column "Mel's Hou Rest, Mel's Lad Rest" does not exist

SELECT * FROM cust WHERE home_location = ANY(string_to_array('Mel''s Hou Rest, Mel''s Lad Rest', ','))
-- OK

请注意，string_to_array 不会移除定界符周围的空格，这可能不符合您的预期。

例如：

-- With whitespace around the delimiter

=> SELECT string_to_array('foo, bar', ',')
 string_to_array 
-----------------
 {foo," bar"}

=> select 'foo' = ANY(string_to_array('foo, bar', ','));
 ?column? 
----------
 t

=> select 'bar' = ANY(string_to_array('foo, bar', ','));
 ?column? 
----------
 f

-- Without extra whitespace

=> SELECT string_to_array('foo,bar', ',')
 string_to_array 
-----------------
 {foo,bar}

=> select 'foo' = ANY(string_to_array('foo,bar', ','));
 ?column? 
----------
 t

=> select 'bar' = ANY(string_to_array('foo,bar', ','));
 ?column? 
----------
 t

当然，在使用查询之前，可以通过规范化输入来抵消这种情况。在某些情况下，可能可以使用 string_to_array(regexp_replace('foo, bar', '\s*,\s', ','), ',') 剥离查询中的空格，但如果没有充分的理由，不要让查询变得更加复杂。

- Henry Heikkinen

---

我在改用单引号后无法正确显示结果，但是关于空格的解释确实帮了我很多，非常感谢！！ :) - MCM13

Answer 2

为了补充一下已接受的答案...

请注意，数组字符串文字表示法（用花括号）'{foo, ba''r, "b{a}z", "b,u,z"}'（等同于更明确的ARRAY['foo', 'ba''r', 'b{a}z', 'b,u,z']）实际上仍然只是一个字符串。要将其用作数组，需要先进行转换，可以通过一些操作隐式地完成（例如ANY()）。但在许多情况下，您需要显式地进行类型转换（例如使用CAST(array_literal as text[])或array_literal::text[]）。

因此，如果将您的第一个表达式重写为以下形式，则应该可以正常工作：

SELECT home_location, subs_state FROM cust
WHERE
    home_location = ANY('{Mel''s Hou Rest, Mel''s Lad Rest}')
    AND subs_state = 'active';

- Michael Ekoka