0

I have a PostgreSQL table, one column of type text contains data like '{123456,345678}'. Is there a way in PostgreSQL to convert this to an array?

yyiws
  • 69
  • 3
  • A convoluted stored procedure parser? If you're brave you can use [PLV8](https://github.com/plv8/plv8) which gives you a whole JavaScript runtime, where parsing this is trivial. – tadman Aug 10 '22 at 19:44
  • So source is Postgres and destination is also Postgres, but an array of ints/text? Are you doing ad-hoc conversions (per query) or creating a new column and persisting converted data? Either way, there is a way to create a new array from text as long as you trust the text is in the right format. But if you have some values that are array-like and some that are not, that will be harder to implement. – ps2goat Aug 10 '22 at 19:48
  • Check out [string functions](https://www.postgresql.org/docs/current/functions-string.html). Specifically, `string_to_array` (to split at comma) coupled with `replace` (to remove braces). – bfris Aug 10 '22 at 19:52
  • have you tried this? https://stackoverflow.com/questions/57961339/convert-string-to-array-postgresql – Foo L Aug 10 '22 at 19:57
  • 5
    You can cast it: `'{123456,345678}'::int[]` –  Aug 10 '22 at 20:00
  • 1
    It already looks like an array of integers, this works fine: SELECT CAST('{123456,345678}' AS INTEGER[]); – Frank Heikens Aug 10 '22 at 20:56

0 Answers0