How to make bulk update with arrays in PostgreSQL?

Question

In PostgreSQL database I have table called services which has 4 columns. Is it possible to make a mass update via arrays? I tried such code but it raise error:

SQL Error [0A000]: ERROR: set-returning functions are not allowed in WHERE

SQL:

UPDATE SERVICES
SET
    NAME = UNNEST(ARRAY['NAME OF THE FIRST SERVICE', 'NAME OF THE SECOND SERVICE']),
    ACTIVE = UNNEST(ARRAY[FALSE, TRUE]),
    DESCRIPTION = UNNEST(ARRAY['DESCRIPTION OF THE FIRST SERVICE', 'DESCRIPTION OF THE SECOND SERVICE'])
WHERE
    ID = UNNEST(ARRAY['e1433cd2-7591-4a74-b910-33ea89d87ecd', '6c27c413-4be2-4a89-bea0-e713445ebfe1']);

Gordon Linoff · Accepted Answer · 2019-08-05 12:03:44Z

3

You can unnest them in a subquery:

UPDATE services s
    SET name = x.name,
        active = x.active,
        description = x.description
FROM (SELECT UNNEST(ARRAY['NAME OF THE FIRST SERVICE', 'NAME OF THE SECOND SERVICE']) as name,
             UNNEST(ARRAY[FALSE, TRUE]) as active,
             UNNEST(ARRAY['DESCRIPTION OF THE FIRST SERVICE', 'DESCRIPTION OF THE SECOND SERVICE']) as description
             UNNEST(ARRAY['e1433cd2-7591-4a74-b910-33ea89d87ecd', '6c27c413-4be2-4a89-bea0-e713445ebfe1']) as id
     ) x
WHERE s.id = x.id;

When there are multiple unnest()s in a single select, Postgres unnests them in parallel (rather than generating a Cartesian product). The number of rows is the number of rows of the biggest array, with shorter arrays being padded with nulls.

edited Aug 5, 2019 at 12:03

answered Aug 5, 2019 at 10:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nurzhan Nogerbek Over a year ago

I think you miss SET keyword in your SQL statement. In my case type of ID column is uuid. When I try to test your code it raise error: ERROR: operator does not exist: uuid = text. Do you have any ideas about it?

Nurzhan Nogerbek Over a year ago

As I understand array of ids must be like this: UNNEST(ARRAY[uuid('e1433cd2-7591-4a74-b910-33ea89d87ecd'), uuid('6c27c413-4be2-4a89-bea0-e713445ebfe1')]).

Nurzhan Nogerbek Over a year ago

Also other way is UNNEST(ARRAY['e1433cd2-7591-4a74-b910-33ea89d87ecd', '6c27c413-4be2-4a89-bea0-e713445ebfe1']::uuid[])

Collectives™ on Stack Overflow

How to make bulk update with arrays in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related