CODEWITHSUNDEEP
sqlarrayspostgresqlexistsany
Johnczek
Johnczek

Reputation: 657

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).

Upvotes: 0

Views: 1818

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656962

I would like to filter rows that has at least one array element that is after some input?

You can use the ANY construct as has been advised.

WHERE '1996-12-31'::timestamp < ANY ('{2021-10-10, 2021-12-20, 1996-01-01}'::timestamp[])

Has to be <, not >, obviously.
Your "timestamps" look a lot like dates - timestamp input accepts that, too.
But always use the recommended ISO 8601 format (as demonstrated), else your input depends on setting of the current session.

See:

But chances are, there is much more efficient way. You speak of "aggregated timestamp data". Typically it's much more efficient to check before aggregating. Not least because that can use indexes, as opposed to your approach. Typically, EXISTS does the job. Something like:

SELECT ...
FROM   tbl t
WHERE  EXISTS (SELECT FROM tbl t1 WHERE t1.id = t.id AND t1.timestamp_column > '1996-12-31'
GROUP  BY ...

Start a new question with details of your query to get a fitting solution.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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.

Upvotes: 3

Related Questions