I'm looking for a solution in postgresql to insert or update a row, based on the timestamp of that row.
What I'm looking for is the following:
I want to insert the use of a specific hashtag, and count this use, based on the last hour.
+--------------------------------------------------+
| hashtag | count | timestamp |
+--------------------------------------------------+
| hello | 1 | 2020-01-01 12:00:00+00.00 |
| world | 3 | 2020-01-01 12:00:00+00.00 |
| hello | 2 | 2020-01-01 11:00:00+00.00 |
| ... |
+--------------------------------------------------+
What I want to achieve:
I want to insert a new row if there is no row for the specific hashtag in the last hour created (based on the timestamp). If a row exists with timestamp > NOW() - INTERVAL '1 HOUR'
, I just want to update the count of that row.
How can I achieve this? Can I do this with a CHECK-constraint?
INSERT ... ON CONFLICT
.timestamptz