PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Display rows where array matches list of values

I’m sorry if this question has been asked before. I’m new to SQL and have been trying to search for an answer but to no avail.

I have a dataset which looks something like this:

Codes Total
1,2,3 4
5 6
5,7 8

‘Codes’ is a varchar column.

I want to create a query that allows me to aggregate ‘Total’ if ‘Codes’ contains any one of the values in an array.

Something like this:

SELECT SUM(total)
FROM table
WHERE (codes) = ANY('5','7')

In which case the SUM should be 14.

What should be the appropriate query? Any help would be much appreciated!

Tags:

2

Answers


  1. 0

    You’re close. You can use string matching ~ with ANY array comparison, pretty much exactly how you already tried:
    demo at db<>fiddle

    SELECT SUM(total)
FROM test
WHERE codes ~ ANY(array['5','7'])
    sum
    14

    If Codes column is supposed to hold arrays of integers, you can reflect that in its definition:

    alter table test
  alter column codes type int[]
  using(string_to_array(codes,',')::int[]);

    That makes it lighter and allows you to use adequate array operators, like overlap &&:

    SELECT SUM(total)
FROM test
WHERE codes && array[5,7];
    Login or Signup to reply.
  2. 0

    Since codes are comma-separated strings, you would be better off converting them to an array and then filtering by the array column—something like this.

    refer to array functions for more details https://www.postgresql.org/docs/current/functions-array.html

    with raw_data as (
    select '1,2,3' as code, 5 as total
    union all
    select '5' as code, 6 as total
    union all
    select '5,7' as code, 8 as total
    union all
    select '7' as code, 10 as total
),
transformed_data as (
    select string_to_array(code, ',') as array_codes, total from raw_data
)
select sum(total) as sum_total
from transformed_data
where array_codes && array ['5', '7'];

-- 24

    https://dbfiddle.uk/jffQaY0v

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search