Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
    FROM table1
    GROUP BY row3, EXTRACT(HOUR FROM "time"), id
TRUNCATE table1;

But this, unfortunately, inserts every row from table1.

share|improve this question
up vote 3 down vote accepted

Say you want to retain the values belonging the max(id) for every hour and for every distinct row3 value (but see the EDIT 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

    [the query above comes here]


Now that we know the truth about UUID :), what about this one?

SELECT DISTINCT first_value(id) OVER (PARTITION BY row3, EXTRACT(hour FROM "time"))
FROM table1

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!)

share|improve this answer
There is just one problem: "function max(uuid) does not exist". I already tried something like this... – icefex Dec 5 '12 at 11:18
@icefex Ugh. So your id is of what type? UUID? Please edit the questino with this information. – dezso Dec 5 '12 at 11:39
Already had that, but made it bold so it does not get overlooked ^^ – icefex Dec 5 '12 at 11:42
Now another problem is this: "" must appear in the GROUP BY clause or be used in an aggregate function – icefex Dec 5 '12 at 12:07
Thank you! It finally works :) – icefex Dec 5 '12 at 13:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.