PostgreSQL 11 Reestablishes Window Functions Leadership | Hacker News Reader

PostgreSQL 11 Reestablishes Window Functions Leadership

modern-sql.com

150 pointsboshomi15 comments

Window functions will always have a special place in my heart as one of the magic moments on StackExchange where I discovered something truly powerful. Many years ago I had been asked as a lowly developer to perform some sort of reasonably complex dimensional reshape on a SQL Server table. I was at a loss so I asked on DBA StackExchange for advice and I got a response providing a glorious 4 line window function that gave me exactly what I needed. It really made me excited about diving deeper into SQL and motivated me enough to become quite good at it. It’s ended up being one of the most invaluable skills to have learned during my engineering career.

I've ended up learning more about SQL than Java at my first job because deploying Java solutions is so slow. What kind of engineering jobs appreciate good SQL skills?

Interesting content, although the title is a little dramatic. 'Reestablishes Leadership' lol

Major Version Update to assert dominance

WHAT PostgreSQL IS DOING TO WINDOW FUNCTIONS, WILL SHAKE YOU TO YOUR CORE!

On a serious note, PostgreSQL is the epitome, IMHO, of an open-source project that is tightly managed, but still able to change with the times and the user-bases changing needs. Really Postgres and Redis are the two projects that I think of when I think about "great" open-source projects.

What they mean is that for years, Postgres had the most advanced support for window functions among the common databases. Then they lost that crown due to an update to MySQL, but now with 11, they are back to being the leader as they have brought in the missing feature plus some additional ones.

Excellent work by the PostgreSQL folks. I did a naive implementation of `ignore nulls` a while back when exploring porting my product from Oracle, but it was slow. Great to see such progress implementing these extremely powerful features.

I'm not sure if it would help, but postgresql also has 'is not distinct from' which is like a two valued version of equality (vs the three valued equality operator).

https://modern-sql.com/feature/is-distinct-from

Just skimming through but on down, is the

fetch first...with ties in Oracle

not the same as

select top (1) with ties from order by SortOrder in SQL Server?

In principle yes, it is even mentioned in footnotes of the matrix (2, 3).

Thank you for compiling the matrices. I think "Parameters in Fetch First|Next" is wrong with regards to SQL Server, it supports "Parameters (?) in fetch first" [1].

[1] https://docs.microsoft.com/en-us/sql/t-sql/queries/select-or...

Very well spotted! I've just fixed that.

One of my test cases was binding that paramter as 'numeric' rather than 'interger'. The other DBs didn't care about that.

@MarkusWindand: Thank you for the Websites [1],[2],[3] and the book »SQL Performance Explained«[4].

[1] https://modern-sql.com/

[2] https://use-the-index-luke.com/

[3] https://db-engines.com/

[4] https://www.amazon.de/SQL-Performance-Explained-Everything-p...

Welcome. But db-engines.com is not my site (but also run by Austrians ;)

I didn't know SQLite had gotten window functions. I have only used them in Postgres because I didn't know they were standard. Whenever I did use them, they were very handy. I used row_number to delete things in little batches like "where row_number between 40,000 and 60,000". And lag() to only print the times a column's value changed, with "case when lag(myColumn) over (myWindow) is null then myColumn else '' end".

I'm glad to hear these are fully supported so I can feel safe learning more about them.

Reply on news.ycombinator.com