将字符串转换为数组 - PostgreSQL

我在表中有一列存储用逗号分隔的名称，例如：“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[])

有什么方法可以完成此任务，而无需我将数据库从“文本”更改为“数组”

SQL 使用单引号字符串文字 https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS。你的绳子"Mel's Hou Rest, Mel's Lad Rest"它周围有双引号，这使得 Postgres 将其解释为带引号的标识符 https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS。您可以使用两个单引号将一个单引号包含在字符串中。

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', ','), ',')但如果没有充分的理由，我不会让查询变得复杂。