Removing duplicate entries for each hour

Question

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.

Answer 1

Generic case

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the UUID section below). The IDs of these you get with a query like

SELECT max(id)
FROM table1
GROUP BY row3, EXTRACT(hour FROM "time")
;

(This will omit the date from your data, which is what you may want—or not. In the latter case you may want to use date_trunc('hour', "time") instead.)

Then you can use the above query to populate a table just as you did in your question, or delete everything else from table1 like

DELETE FROM table1
WHERE id NOT IN (
    [the query above comes here]
)
;

UUID

There is no max() for UUID. Therefore another solution is needed in your case, like this one:

SELECT DISTINCT first_value(id) OVER w
FROM table1
WINDOW w AS (PARTITION BY row3, EXTRACT(hour FROM "time")
             RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
;

This one applies a window function, and the grouping is achieved by the PARTITION BY clause and DISTINCT.

(Acknowledgment: thanks for the idea for AndriyM!)