Convert String to Array - PostgreSQL

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'

What is the column that holds the comma-separated string? And what are you trying to accomplish with the query? — GMB, Sep 16 '19 at 17:06

Henry Heikkinen · Accepted Answer · 2019-09-16T17:38:16.477

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.

I was having trouble displaying the correct results after changing to single quotes but the explanation for the white space really helped me, thank you very much!! :) — MCM13, Sep 16 '19 at 17:51

score 0 · Answer 2 · answered Jul 27 '22 at 02:20

To supplement the accepted answer a bit...

Note that the array string literal notation (with curly braces) '{foo, ba''r, "b{a}z", "b,u,z"}' (equivalent to the more explicit ARRAY['foo', 'ba''r', 'b{a}z', 'b,u,z']) is still actually just a string. To be used as an array it needs to first be converted, which a few operations can do implicitly (like ANY()). In many cases though, you'd need to explicitly cast it (e.g. with CAST(array_literal as text[]) or array_literal::text[]).

Your first expression should therefore work if rewritten as

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

Convert String to Array - PostgreSQL

2 Answers2

Linked