I recently asked the same question about MySQL, which has since been migrated to Postgres. Old question.
To sum it up:
I have a table with an id (type UUID), a timestamp, and some text (row3). How do I retain only one entry per hour for each distinct value of row3?
What I tried is this:
INSERT INTO log_table SELECT * FROM table1 WHERE id IN ( SELECT DISTINCT ON(id) id FROM table1 GROUP BY row3, EXTRACT(HOUR FROM "time"), id ); TRUNCATE table1;
But this, unfortunately, inserts every row from table1.