Postgres union of queries in loop

Question

I have a table with two columns. Let's call them array_column and text_column

I'm trying to write a query to find out, for K ranging from 1 to 10, in how many rows does the value in text_column appear in the first K elements of array_column

I'm expecting results like:

k    |    count
________________
1    |    70
2    |    85
3    |    90
...

I did manage to get these results by simply repeating the query 10 times and uniting the results, which looks like this:

SELECT 1 AS k, count(*) FROM table WHERE array_column[1:1] @> ARRAY[text_column]
UNION ALL
SELECT 2 AS k, count(*) FROM table WHERE array_column[1:2] @> ARRAY[text_column]
UNION ALL
SELECT 3 AS k, count(*) FROM table WHERE array_column[1:3] @> ARRAY[text_column]
...

But that doesn't looks like the correct way to do it. What if I wanted a very large range for K?

So my question is, is it possible to perform queries in a loop, and unite the results from each query? Or, if this is not the correct approach to the problem, how would you do it?

Thanks in advance!

user330315 · Accepted Answer · 2019-02-04 19:42:33Z

You could use array_positions() which returns an array of all positions where the argument was found in the array, e.g.

select t.*, 
       array_positions(array_column, text_column)
from the_table t;

This returns a different result but is a lot more efficient as you don't need to increase the overall size of the result. To only consider the first ten array elements, just pass a slice to the function:

select t.*, 
       array_positions(array_column[1:10], text_column)
from the_table t;

To limit the result to only rows that actually contain the value you can use:

select t.*, 
       array_positions(array_column[1:10], text_column)
from the_table t
where text_column = any(array_column[1:10]);

To get your desired result, you could use unnest() to turn that into rows:

select k, count(*)
from the_table t, unnest(array_positions(array_column[1:10], text_column)) as k
where text_column = any(array_column[1:10])
group by k
order by k;

George S · Accepted Answer · 2019-02-04 19:51:19Z

1

You can use the generate_series function to generate a table with the expected number of rows with the expected values and then join to it within the query, like so:

SELECT t.k AS k, count(*) 
FROM table 
--right join ensures that you will get a value of 0 if there are no records meeting the criteria
right join (select generate_series(1,10) as k) t 
 on array_column[1:t.k] @> ARRAY[text_column]
group by t.k

This is probably the closest thing to using a loop to go through the results without using something like PL/SQL to do an actual loop in a user-defined function.

edited Feb 4, 2019 at 19:51

answered Feb 4, 2019 at 19:43

George S

2,19113 silver badges14 bronze badges

Collectives™ on Stack Overflow

Postgres union of queries in loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related