Compare a column with every element of another column which is an array

user_19924377 0
Viewed 31

I have 3 columns in a postgresql table like:

col A            col B        col C                                     
----------      ---------   ---------                                 
2020-01-01      2024-01-01   {2020-01-01, 2020-05-01, 2022-03-01}    
2020-05-01      2021-05-01   {2020-01-01, 2020-05-01, 2022-03-01}
2022-03-01      2023-03-01   {2020-01-01, 2020-05-01, 2022-03-01}

col C is basically the array_agg of colA over the window. What I need to check is, for each row of col A, if the datetime is >= any of the elements of the array from col C. What is the possible solution?

Note: In my actual case there's another col D, which is the array_agg of col B. So what I'll be actually checking is col A >= any of the elements of the array from col C and col B <= any of the elements of the array from col D. I mainly don't know how to compare a value with each element from an array.

postgresql
1 Answers

The syntax here is pretty nice, you can just write

WHERE A >= Any( C )

If you need to do more complicated checks on the elements in an array, you can also use a generator expression to make it act like multiple rows and then write SQL against it. For example,

WHERE 0 < (SELECT COUNT(*) FROM unnest(C) AS elt WHERE A >= elt)

Would be a more elaborate (but more general) way to do the same thing.

user_17475830
Related