Using array functions in where clause

Question

Suppose I have a table my_table that has the following structure:

id::int   counts::array
--------------------
02        {0,0,0,0,0,0,0}
03        {10,0,0,20,40,10,0}
04        {0,0,0,0,0,0,0}
05        {0,20,30,20,0,10,10}
06        {0,0,0,27,0,50,4}
07        {1,0,0,0,0,0,0}
08        {0,0,0,0,0,0,0}

I would like to run the following query, presented in pseudo code:

SELECT id, counts FROM my_table
WHERE NOT SUM(ARRAY_TO_STRING(counts, ', ')::int) = 0

I know I cannot use aggregate functions in a where clause, but what would be the quickest way to do this in PSQL?

score 3 · Accepted Answer · 2019-02-18 15:48:39Z

3

You need to turn all elements of the array into rows to be able to sum them:

select mt.id, mt.counts
from my_table mt
where (select sum(i) from unnest(mt.counts) as t(i)) <> 0;

You could create function to make that easier:

create function int_array_sum(p_array int[])
  returns bigint
as
$$
   select sum(i) from unnest(p_array) as t(i);
$$
language sql;

Then you can use:

select mt.id, mt.counts
from my_table mt
where int_array_sum(mt.counts) <> 0;

Collectives™ on Stack Overflow

Using array functions in where clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related