I have a query in Postgres. It's working well and returns exactly the rows I'm looking for.

Here's the query:

select distinct on (status) id, status, timestamp
from my_table
where id = 1
order by status, timestamp
limit 500

This is what it returns:

How can I pivot this table to return this exactly:

CodePudding user response：

You could use the extension tablefunc, but it looks like a simple aggregate using a filter already works:

SELECT  id
    ,   MIN(timestamp) FILTER (WHERE status = 'started') AS started
    ,   MIN(timestamp) FILTER (WHERE status = 'paused') AS paused
    ,   MIN(timestamp) FILTER (WHERE status = 'killed') AS killed
FROM    my_table
WHERE   id = 1
GROUP BY id;