The first feature in the deck is FILTER, which I can't help but mention I added support for (with a case/when fallback) to Django 2.0. So, if you're using the new Django release you can use this syntax on any aggregate/annotate via the filter kwarg[1] now.<p>There was one user report on the github PR that said FILTER was 10-15% faster over the equivalent CASE/WHEN, due to the null filtering I think. It's a shame only Postgres supports this supposedly standard syntax.<p>Edit: Heh, I initially got (stole) the idea of using FILTER + CASE from modern-sql.com[2], which is run by the author of this slide deck. So I guess I aught to thank Markus for this!<p>1. <a href="https://docs.djangoproject.com/en/2.0/ref/models/conditional-expressions/#conditional-aggregation" rel="nofollow">https://docs.djangoproject.com/en/2.0/ref/models/conditional...</a><p>2. <a href="http://modern-sql.com/feature/filter" rel="nofollow">http://modern-sql.com/feature/filter</a>
I learned a few things, despite having used Postgres for many years: (1) the filter clause with aggregates to more succinctly pivot tables, (2) the not-valid clause with check constraints to apply only to new data while leaving old rows alone, and (3) something about inverse-distribution functions --- I actually didn't learn these yet but bookmarked it for later, because it's too hard to read on a Saturday ;)<p>It really is amazing how much you can prepare your data before it leaves your database, or how raw you can leave your data before sending it there, letting Postgres take care of it. I wonder how much middle code would be saved (PHP, Python, Perl, etc.) if more programmers knew more SQL, and especially if they chose Postgres more often. I also wonder why SQL seems to be the language that developers know least, even though it seems like it should be easiest, with its declarative, natural-sounding syntax. (I admit that the naturalness is deceptive. Just because "select color, count(1) from favorites where age < 13 having count(1) > 3 order by 1" sounds like English doesn't mean you can tell exactly what it will do before reading the manual.)
It was a good talk at fosdempgday. Well paced, funny at times, esp the part comparing booleans with mysql "booleans" and "check constraints" (spoiler neither boolean nor checked)<p>Too bad I'm stuck on 9.1 for the time being which lack many of these really nice features.
It might be worthwhile to add cloud data warehouses with SQL interfaces (e.g. Redshift/BigQuery) to the slides, as I believe they have a similar feature set to PostgreSQL.
Postgres isn't always perfect and for a few things lagged behind for a few years (namely upsert and better replication support). Fortunately we got both of these in recent releases, but this post is a great collection of areas where it already shined with some powerful features.<p>In particular really love the details on check constraints and null in Postgres.