Question 418816 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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].

Tags:

2

Answers


  1. 0

    I believe its because jsonb_array_elements returns a set of rows instead of single value. As mentioned in the comments , I tried a similar approach to create a new column and then updating the new column

    Fiddle

    ALTER TABLE MyTable
ADD COLUMN new_array_column int[];

UPDATE MyTable
SET new_array_column = (
    SELECT array_agg(elem::int)
    FROM jsonb_array_elements_text(MyColumn) AS elem
);

ALTER TABLE MyTable
DROP COLUMN MyColumn;

ALTER TABLE MyTable
RENAME COLUMN new_array_column TO MyColumn;

    Output

    id mycolumn
    1 {25,32}
    2 {10,20,30}
    Login or Signup to reply.
  2. 0

    jsonb_array_elements() returns a set of jsonb, not an array of integer. So twice incorrect.

    You might use jsonb_array_elements_text() instead, cast to integer and aggregate that to an array, but that requires a subquery and ALTER TABLE does not allow that in the transform expression.

    Solution

    Create a tiny auxiliary function for the task:

    CREATE OR REPLACE FUNCTION jsonbarr_to_intarr(_js jsonb)
  RETURNS int[]
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
RETURN ARRAY(SELECT jsonb_array_elements_text(_js)::int);

    Then you can proceed with ALTER TABLE as planned:

    ALTER TABLE tbl ALTER COLUMN col TYPE int[] USING jsonbarr_to_intarr(col);

    fiddle

    See:

    If you don’t want to keep the function around, make it temporary:

    CREATE OR REPLACE FUNCTION pg_temp.jsonbarr_to_intarr(_js jsonb) ...

    See:

    Note that ALTER TABLE is fundamentally different from UPDATE!
    The former locks the whole table while doing a rewrite, which is much simpler and typically substantially faster. But blocking concurrent access must be avoided in some databases, especially if the table is huge, so it would take a long time. Then, incrementally populating a new column may be the way to go (even if that’s a lot more complex and expensive overall).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search