Postgresql, cast character varying list to array-CodePudding

Have a table with a character varying column with values such as

['abcd1234', 'defghij13']

When trying to test with string_to_array function

select string_to_array('["abcd1234"]', ', ')

returns {"[\"abcd1234\"]"}

Can this be done within postgres directly, rather than using Python?

EDIT: the quotes are single but if not converted to double quotes inside the brackets, receive a syntax error

ERROR:  syntax error at or near "abcd234"
LINE 1: select string_to_array('['abcd234']', ', ')

Expected result below.

CodePudding user response：

Postgres array literals use curly brackets, not square brackets.

Change [ and ] chars to { and }, then cast:

replace(replace(myColumn, '[', '{'), ']', '}')::text[]