News for hackers. Stuff that matters.
Re:Postgres Hidden Gems by Klathmon on Wednesday January 31, 2018 @10:31PM (#16279469)
I'm going to echo the comment about aggregate functions.

I don't know how "hidden" they are, but they are one hell of a gem!

At this point our one application has more SQL code in it than application code. The postgresql json* aggregate functions make querying and formatting data easy enough that it can be done directly in the database and the result basically piped to the output without any need to reformat it.

I'll also add CTEs. They might also not be very "hidden" but they are fantastic once you get used to using them.

Re:Postgres Hidden Gems by sk5t on Wednesday January 31, 2018 @11:11PM (#16279597)
CTEs are very nice, although their performance characteristics push me over to lateral joins and subqueries by default.
Re:Postgres Hidden Gems by Klathmon on Wednesday January 31, 2018 @11:13PM (#16279600)
lateral joins were already in the list so I skipped them, but I agree. I tend to reach for CTEs first as they are more readable in my opinion, but when there are perf issues, lateral joins are the way to get out of them.

They almost feel like cheating!

Re:Postgres Hidden Gems by garyclarke27 on Thursday February 01, 2018 @07:45AM (#16281455)
I agree Lateral Joins are amazing, Ive started using them instead of CTEs a lot recently. Set returning functions are automatically lateral joined and make the logic much easier to follow than sub queries. Performance is great because Postgres happily inlines most SQL functions - unlike my experience with SQL Server (several years ago may have changed) where UDFs where munch slower than sub queries.