CODEWITHSUNDEEP
sqlpostgresql
Nurzhan Nogerbek
Nurzhan Nogerbek

Reputation: 5246

How to make bulk update with arrays in PostgreSQL?

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']);

Upvotes: 1

Views: 1891

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 3

Related Questions