News for hackers. Stuff that matters.
Re:Distinct on: a confusing, unique and useful feature in Po... by Tostino on Thursday March 19, 2020 @09:45AM (#22627406)
Distinct on is for sure one of the most useful Postgres extensions to SQL, as far as how concise it makes queries which otherwise would suck to write.

I would have liked to see equivalent queries written with GROUP BY so people could understand and translate that knowledge even if they don't have direct Postgres experience.

Thanks for the article!

Re:Distinct on: a confusing, unique and useful feature in Po... by claytonjy on Friday March 20, 2020 @03:06PM (#22640539)
I believe the demo query

    SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;
could also be written with a self-join like so

    SELECT location, time, report
    FROM weather_reports
    JOIN (
        SELECT location, max(time) AS time
        FROM weather_reports
        GROUP BY location
    ) as _ USING (location, time)
    ORDER BY location;
though I'd be curious what the query-plan differences are. I suspect the DISTINCT ON might translate more closely to a lateral join

    SELECT
    FROM (
        SELECT DISTINCT(location)
        FROM weather_reports
    ) AS locations
    JOIN LATERAL (
        SELECT location, time, report
        FROM weather_reports
        WHERE location = locations.location
        ORDER BY ts DESC
        LIMIT 1
    ) AS reports ON TRUE
    ORDER BY location;
But I really can't say.
Re:Distinct on: a confusing, unique and useful feature in Po... by paulddraper on Friday March 20, 2020 @07:59PM (#22642995)
The first only works if location,time is unique. In the example it may be, not necessarily in general.

And yes, the JOIN LATERAL would be a good substitute.

P.S. More concise is CROSS JOIN ... rather than JOIN ... ON TRUE

Re:Distinct on: a confusing, unique and useful feature in Po... by zeroimpl on Friday March 20, 2020 @09:52PM (#22643560)
In general if you query a table twice, the query planner will visit that table twice. So DISTINCT ON is almost always more efficient.

The main exception I’ve seen is when using it in a view, if you try to filter the results of the view, it usually can’t optimize a DISTINCT ON, but sometimes can for GROUP BY (though that might have improved on newer versions)

Re:Distinct on: a confusing, unique and useful feature in Po... by beckingz on Friday March 20, 2020 @01:56PM (#22639696)
This would be great, because getting those same results in standard SQL or MariaDB is a pain.