Search code examples
sqlarrayspostgresqlexistsany

Greater than any element of array

I am facing this issue:

Is there a way in PostgreSQL to put aggregated timestamp data into an array (for example using array_agg function) and then perform any match on some condition?

I am doing something similar with LIKE on aggregated strings (using string_agg(column,';')). But how to perform something similar on timestamps?

So if result would be '{10.10.2021,20.12.2021,1.1.1996}' as timestamp_array and I would like to filter rows that have at least one array element that after some input?

For example, ... WHERE 31.12.2021 > timestamp_array ... would not match the row above cause there is no array element after 31.12.2021.

But If I query ... WHERE 31.12.1996 > timestamp_array ..., the row above would be matched (cause at least one element of the array is in given interval).

Solution

  • First, you would use standard date formats. Then you can use:

    where '2021-12-31' > any (timestamp_array)

    Here is a db<>fiddle to illustrate the idea.