Answer a question

I have a column in a table that stores names separated by commas, example: "Mel's Hou Rest, Mel's Lad Rest". What I need is to convert this string into an array separated by commas.

The query I need is:

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

I have tried this, but I keep getting an error:

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

Is there any way to accomplish this without me having to change the database from 'text' to 'array'

Answers

SQL uses single quotes for string literals. Your string "Mel's Hou Rest, Mel's Lad Rest" has double quotes around it which makes Postgres interpret it as an quoted identifier. You can use two single quotes to include one in the string.

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

Also note that string_to_array does not remove whitespace around the delimiter which might not be what you expect.

For example:

-- 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

This of course can be countered by normalising the input before using it in the query. In somes cases it might be feasible to strip the whitespace in the query with string_to_array(regexp_replace('foo, bar', '\s*,\s', ','), ',') but I would not complicate the queries like that without a good reason.