sqlarrayspostgresqljsonb

Postgres UPDATE int[] column with values from jsonb column

I have a JSONB column with an array of integers and I'd like to migrate it to a native column of array of integers:

ALTER TABLE MyTable
ALTER COLUMN MyColumn TYPE int[] USING jsonb_array_elements(MyColumn)

However, this does not work:

set-returning functions are not allowed in transform expressions

MyColumn values look like this: [25,32].

Solution

  • Since I didn't want to add a function for something so simple I did the following:

    ALTER TABLE MyTable
ALTER COLUMN MyColumn TYPE int[] USING TRANSLATE(MyColumn, '[]', '{}')::int[]