1

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}'.

5

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
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
2

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

But the demonstrated form of UPDATE carries several potential problems:

  1. It's typically wise to add a condition excluding empty updates. See:

    Your particular query is not affected by this, since the ARRAY constructor never returns NULL and you only update NULL values.

  2. 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.)

  3. 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?

Related:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.