4 ms·
CREATE TABLE logs ( id SERIAL PRIMARY KEY, log_time TIMESTAMP NOT NULL, message TEXT ) PARTITION BY RANGE (log_time); Why won't thi
by MuffinFlavored 2y ago
CREATE TABLE logs (
id SERIAL PRIMARY KEY,
log_time TIMESTAMP NOT NULL,
message TEXT
) PARTITION BY RANGE (log_time);
Why won't this work on stock PostgreSQL?
- gonzo41 2y agoread the docs, it's not saying that won't work. This extension along with timescale just makes some things more ergonomic.
- darkstar_16 2y agoAnd provides helper functions to deal with regular time series tasks
- jmaker 2y agoThat won’t work already because your timestamp isn’t part of your primary key.
- kbolino 2y agoI think what's meant here is windowing (partitioning the query) not partitioning the table per se. Though even with this strategy, you must manually create new partitions all the time. This also isn't typical time-series data, which generally stores numbers. Supposing you had a column "value INTEGER" as well, how do you do something like the following (pseudo-SQL)? SELECT AVG(value) AS avg_value FROM logs GROUP BY INTERVAL '5m' Which should output rows like the following, even if the data were reported much more frequently than every 5 minutes: log_time | avg_value 2024-05-20T00:00:00Z | 10.3 2024-05-20T00:05:00Z | 7.8 2024-05-20T00:10:00Z | 16.1
- ants_a 2y agoSELECT date_bin('5 minutes', log_time, '2000-01-01') log_time, AVG(value) avg_value FROM logs GROUP BY 1