Reputation: 63
How do I schedule performant string extraction in postgreSQL and timescaledb?
I want to extract unique dates from a string to use as a selector for a realtime time series database. I use the DISTINCT date as a variable in grafana to limit the records that are displayed. Whenever there is a new date, I extract the date and return DISTINCT values.
A query on the fly does a table scan, which of course is very slow.
The idea of a real-time aggregate seems attractive, but timescale won't permit a DISTINCT clause in the aggregate.
Example data:
| time | sensor | temp |
|----------------------- |------------|------|
| 2024-08-20 12:20:50-07 | A_20240820 | 98 |
| 2024-08-20 12:25:50-07 | B_20240820 | 102 |
| 2024-08-21 12:20:50-07 | A_20240821 | 105 |
| 2024-08-22 12:20:50-07 | A_20240822 | 103 |
Desired result:
| date |
| -------- |
| 20240820 |
| 20240821 |
| 20240822 |
This does NOT work:
CREATE MATERIALIZED VIEW temp_dates WITH (timescaledb.continuous) AS SELECT DISTINCT RIGHT(sensor,8) AS date FROM temps_idy;
ERROR: invalid continuous aggregate query
DETAIL: DISTINCT / DISTINCT ON queries are not supported by continuous aggregates.
Upvotes: 0
Views: 49
Reputation: 928
Select
right(sensor, 8),
count(right(sensor,8)) -- not required, can be removed
From
Temps_idy
Group by
Right(sensor, 8)
Order by
Right(sensor, 8) desc -- desc optional
Upvotes: 0