clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799187 fiddles created (41761 in the last week).

CREATE TABLE history ( -- partition parent fooid text NOT NULL, target text NOT NULL, updated_at timestamp NOT NULL DEFAULT now() ) PARTITION BY RANGE (updated_at);
 hidden batch(es)


CREATE TABLE history_past PARTITION OF history FOR VALUES FROM (MINVALUE) TO ('2017-09-15 0:0'); -- partition for timestamps before 2017-09-15 ("past") CREATE TABLE history_20170915 PARTITION OF history FOR VALUES FROM ('2017-09-15 0:0') TO ('2017-09-16 0:0'); -- partition 2017-09-15 ("today"); CREATE TABLE history_20170916 PARTITION OF history FOR VALUES FROM ('2017-09-16 0:0') TO ('2017-09-17 0:0'); -- partition 2017-09-16 ("tomorrow"); -- more tables ahead of time ...
 hidden batch(es)


INSERT INTO history VALUES ('foo', 'target1', '2017-09-14 01:00') , ('foo', 'target2', '2017-09-14 02:00') , ('foo', 'target3', '2017-09-14 03:00') , ('foo', 'target4', '2017-09-14 04:00') , ('foo', 'target5', '2017-09-14 05:00') -- already 5 rows for 'foo' in the past , ('foo', 'target6', '2017-09-15 11:00') -- two more "today" , ('foo', 'target7', '2017-09-15 12:00') , ('bar', 'target8', '2017-09-15 02:00') , ('foo', 'target9', '2017-09-16 23:00'); -- tomorrow is always ready to go
9 rows affected
 hidden batch(es)


TABLE history;
fooid target updated_at
foo target1 2017-09-14 01:00:00
foo target2 2017-09-14 02:00:00
foo target3 2017-09-14 03:00:00
foo target4 2017-09-14 04:00:00
foo target5 2017-09-14 05:00:00
foo target6 2017-09-15 11:00:00
foo target7 2017-09-15 12:00:00
bar target8 2017-09-15 02:00:00
foo target9 2017-09-16 23:00:00
 hidden batch(es)


TABLE history_20170915;
fooid target updated_at
foo target6 2017-09-15 11:00:00
foo target7 2017-09-15 12:00:00
bar target8 2017-09-15 02:00:00
 hidden batch(es)


TABLE history_20170916;
fooid target updated_at
foo target9 2017-09-16 23:00:00
 hidden batch(es)


-- trim excess from new day (rare case!) DELETE FROM history_20170915 h USING ( SELECT fooid, updated_at , row_number() OVER (PARTITION BY fooid ORDER BY updated_at DESC) AS rn FROM history_past ) d WHERE d.fooid = h.fooid AND d.updated_at = h.updated_at AND d.rn > 5; -- trim excess from past to make room for new rows DELETE FROM history_past h USING ( SELECT fooid, updated_at, new_rows , row_number() OVER (PARTITION BY fooid ORDER BY updated_at DESC) AS rn FROM ( SELECT fooid, count(*) AS new_rows FROM history_20170915 GROUP BY 1 ) ct JOIN history_past p USING (fooid) -- only consider the *few* for which new rows are actually added ) d WHERE d.fooid = h.fooid AND d.updated_at = h.updated_at AND d.rn + d.new_rows > 5; ALTER TABLE history DETACH PARTITION history_past; INSERT INTO history_past -- identical structure guaranteed TABLE history_20170915; -- short syntax DROP TABLE history_20170915; ALTER TABLE history ATTACH PARTITION history_past FOR VALUES FROM (MINVALUE) TO ('2017-09-16 0:0'); -- "yesterday" added to the past -- CREATE TABLE history_20170917 PARTITION OF history FOR VALUES FROM ('2017-09-17 0:0') TO ('2017-09-18 0:0'); -- should rahter be there ahead of time
2 rows affected
3 rows affected
 hidden batch(es)


TABLE history;
fooid target updated_at
foo target3 2017-09-14 03:00:00
foo target4 2017-09-14 04:00:00
foo target5 2017-09-14 05:00:00
foo target6 2017-09-15 11:00:00
foo target7 2017-09-15 12:00:00
bar target8 2017-09-15 02:00:00
foo target9 2017-09-16 23:00:00
 hidden batch(es)