Postgresql, cast character varying list to array

Question

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.

`['abcd1234', 'defghij13']` is not `varchar` value. To your question add the table definiion as text(**not an image**). — Adrian Klaver, Jan 19 '23 at 19:53
Your problem ['abcd1234', 'defghij13'] **is an array** not a string. In addition to table definition (ddl) also post sample as would occur in the table and the results of that data your looking for. Finally, Postgres v9,1 really. That version reached EOL in 2016. It is time you seriously considered updating. — Belayer, Jan 19 '23 at 20:10
If I understand correctly, I think you need to strip off characters `[`, `]`, and `'` from your string and then split it. So either nested `replace()` functions inside the `string_to_array` or `regexp_replace()` inside `string_to_array`. — bfris, Jan 19 '23 at 20:26
Added a picture of how its stored in the database, and how the output should be — Jenobi, Jan 19 '23 at 20:52
1) Again **do not use images**, use text. 2) In `psql` do `\d — Adrian Klaver, Jan 19 '23 at 21:04

score 2 · Accepted Answer · answered Jan 19 '23 at 21:15

2

Postgres array literals use curly brackets, not square brackets.

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

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

answered Jan 19 '23 at 21:15

Bohemian

1 Answers1