UPDATE BIGINT[] column from a SELECT

Question

I have to update a bigint[] column named permissao_ver.
Example value: '{1,2,3,4,5,11,44,56,75,11}'.

Pseudo code:

UPDATE callcenter.pausa  
SET permissao_ver = '{"(SELECT cod_grupo FROM crm.usuariosgrupos
                        WHERE habilitar = 1)"}'::bigint[] 
WHERE habilitado = 1 AND permissao_ver is null

I need to get the codes the above select will return in a format matching the bigint[] column. Like: '{XX, XX, XX, X, XX, X}'.

score 5 · Accepted Answer · answered Jan 25 '17 at 20:33

Use array:

UPDATE callcenter.pausa  
SET permissao_ver = array(SELECT cod_grupo 
                          FROM crm.usuariosgrupos
                          WHERE habilitar = 1)
WHERE habilitado = 1 
  AND permissao_ver is null

score 2 · Answer 2 · answered Jan 25 '17 at 20:40

2

Yet another option

UPDATE callcenter.pausa  
SET permissao_ver = (SELECT array_agg(cod_grupo) 
                     FROM crm.usuariosgrupos
                     WHERE habilitar = 1)
WHERE habilitado = 1 
  AND permissao_ver is null

answered Jan 25 '17 at 20:40

David דודו Markovitz

3,214
13
21

score 2 · Answer 3 · edited May 23 '17 at 12:40

@a_horse provided a solution with an ARRAY constructor that answers your immediate question.

But the demonstrated form of UPDATE carries several potential problems:

It's typically wise to add a condition excluding empty updates. See:
- How do I (or can I) SELECT DISTINCT on multiple columns?
Your particular query is not affected by this, since the ARRAY constructor never returns NULL and you only update NULL values.
The UPDATE happens, no matter what the (uncorrelated) subquery returns. If the subquery finds no row, you overwrite NULL with an empty array ('{}'). And the next call of your query will not touch these rows again as instructed by the predicate permissao_ver is null. ('{}' IS NULL evaluates to FALSE.)
If the subquery expression involves any volatile functions, it is evaluated repeatedly for every target row. This may or may not be desirable. Think of random() - you'd want it evaluated repeatedly. But for an actual constant this would be a (big) waste. It depends on what you want to achieve. If you wrap the computation in a subquery or CTE, it's evaluated once only.

Generally, this form is preferable:

UPDATE callcenter.pausa p
SET    permissao_ver = u.a
FROM  (
   SELECT ARRAY(SELECT cod_grupo
                FROM   crm.usuariosgrupos
                WHERE  habilitar = 1)
   ) u(a)  -- a constant expression?
WHERE  p.habilitado = 1
AND    p.permissao_ver IS NULL
AND    u.a <> '{}';  -- for this particular query?
-- AND    p.permissao_ver IS DISTINCT FROM u.a;  -- more commonly this

Aside: It's odd, that your subquery is uncorrelated (effectively a constant). Typically I would expect some kind of relation to the target table callcenter.pausa?

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

UPDATE BIGINT[] column from a SELECT

3 Answers3