Hacker News Reader

For windows functions a good tutorial can be found at http://tapoueh.org/blog/2013/08/20-Window-Functions

Postgresql also has good support for SQL-99 : http://www.slideshare.net/MarkusWinand/modern-sql

WITH clauses (CTE) are great, but they are "optimization fences" in Postgresql : http://blog.2ndquadrant.com/postgresql-ctes-are-optimization...

I did not know that about CTEs. Thanks!

Window functions are not a Postgres thing, but part of the SQL standard with (varying, of course) support across most of the major RDBMS's.

I've seen comments like this several times that seem to imply that Postgres is exceptional either due to having window functions, or like in this one where the tone sounds as if Postgres does them particularly better.

I'm curious, as I do almost 100% of my production work in MS SQL Server, where I am able to do everything that comes up when "Postgres window functions" are mentioned, whether there is extra functionality in Postgres compared to other RDBMS's window function implementations?

Is there a reason that Postgres seems to get special attention for window functions?

Thanks.

I think it's largely a question of community.

Many users of MS SQL or Oracle learned advanced SQL or platform-specific features which they take for granted. They work at companies that spend lots of money for DBs and hire people who use them very well.

Postgres also has a core community of very knowledgeable folks. But most of its user base, and especially those new to using it, would otherwise use MySQL. They only know small bits of SQL, have only a basic understanding of why any company would need a DBA, and only learn things like this when they absolutely need them for a task. And, for that group of people, MS and Oracle probably aren't serious choices, so the fact that a free database has these cool features seems exciting to them.

(Note that I don't say this with distain. I'm a former-mysql user who now uses postgres on Heroku and am constantly learning things like this. I wouldn't even have understood your perspective until I started working with people who knew Oracle and MS SQL so thoroughly.)

> so the fact that a free database has these cool features seems exciting to them

It IS exciting to them. And to anybody who can't afford Oracle or other expensive enterprise solutions.

Thanks very much. It is helpful to understand more of the background from someone who's seen multiple sides of the situation.

For my work especially (BI consulting), fluency in SQL is incredibly useful, and specifically I don't think there's a single person at my company who doesn't use window functions regularly in all their development work. Other advanced constructs are very common as well.

Again, I appreciate understanding the world outside my own little bubble. Thanks.

This may be related to how long Postgres has had support for various window functions, vs. MSSQL only gaining equivalent support in v2012 (AFAIK). Also, the various query optimization struggles older versions of MSSQL had.

http://sqlperformance.com/2013/03/t-sql-queries/the-problem-...

I think because Postgres is commonly being considered as an alternative to MySQL rather than MSSQL or Oracle.

This does make some sense. Sometimes I forget how abnormal my own frame of reference is in comparison with the typical (at least of the vocal subset) HN poster.

I dunno, getting the hell off Oracle to Postgres is fashionable as hell. We're doing it and EVERYTHING IS BETTER.

Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these. So it may be fashionable but I don't know anyone who is doing it.

> Half the reason you go with solutions like Oracle is because of (a) enterprise support and (b) easy access to talent pool. PostgreSQL has neither of these.

If you need enterprise support for Postgres, there are vendors that offer it (EnterpriseDB is probably the closest to a "first party" equivalent.)

Our third-party Oracle support were most keen to offer PG support. They can tell which way the wind's blowing.

(We haven't taken them up on it. We seriously can't see how we'll need it. But if you do need it, it's there.)

Sure. But they are few and far between and absolutely do not have the same SLAs as someone like an Oracle, NEC, IBM etc. They are generally mom+pop shops. It doesn't really cut it for enterprises which is generally where you are finding Oracle instances.

That's half the justification. Aaand it turns out the justification doesn't hold against practice.

We're discovering that literally everything is better with Postgres. Mostly because instead of a single expensive point of failure, every app gets its own clustered PG pair. Because we can, because we don't have to think about licensing ever again.

Just everything not having to play nicely with anything else makes a huge difference.

The other nice thing is that PG is administerable by clear-thinking (and understand relational databases) non-specialists who can read a manual. You don't actually need big-ticket support unless you do.

And, guess what? Our Oracle support was most keen to offer Postgres support, because they too can tell which way the wind is blowing.

(PG 9.3 out of Ubuntu 14.04 repos. Failover pair with a primary and standby. Primary streams write-ahead log records to standby as they’re generated. Some script gaffer-tape to watch for primary failure and fail over (I think we haven’t ever yet actually had to invoke this). Conversions done by hand with ora2pg then faff and twiddling and unit tests. Gotchas: malformed sql that Oracle accepts but PG chokes on. All cobbled together just following the docs, almost certainly better ways to do all this.)

As for anyone else doing it ... we were buying AppDynamics (which is frickin' awesome btw) and talking to them about our plans to move from Oracle to PG. They said quite a few of their customers were thinking similarly. So maybe it's our own personal bubbles differing, but I think it's happening in at least some quarters.

AppDynamics which I know very well is used by web shops. Very few of those are using Oracle and it wouldn't surprise me at all if they were moving to something else.

The majority of Oracle instances however are in the back rooms of enterprises.

Yeah, that's pretty plausible.

Key point: it's possible. And unlike MySQL, Postgres is actually a proper database that can do what Oracle, MSSQL etc do. (We're also looking askance at our remaining MSSQL and have replaced one instance of the two with PG.)

Whenever I read articles like this, Postgres seems to have so many more small niceties and syntaxes than MSSQL though (I don't have a list of things, it just seems "easier" and more flexible).

I don't disagree in the general case, but I'm particularly interested in window functions here, as they are part of the SQL standard and seem equivalent between MS SQL Server and Postgres (I don't have near enough experience in other RDBMS's to speak to their implementations), despite being called out with some regularity as a Postgres-specific nicety.

The only one I know of is that PostgreSQL has some nice ordinary aggregate functions which can be used well with window functions, like bool_and() and array_agg(). I can't think of an example on top of my head where it is useful but I know I have used them both.

Thanks. There are definitely features in Postgres that I'm jealous of in the MS SQL Server world, and you've listed a couple of them.

There are window functions in sql server, too

https://www.simple-talk.com/sql/learn-sql-server/window-func...

I am well aware. My confusion is, therefore, why do window functions get called out specifically, fairly commonly, as a Postgres-specific piece of awesomeness.

Window functions are awesome, and they are a part of the SQL standard, thus my confusion. As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.

> As a sibling to your post pointed out, Postgres is often being compared to MySQL, which leads to highlighting the differences between these two, ignoring the features of DB2, MS SQL Server, and Oracle.

License issues (and not just licensing costs, though that's often a factor) often mean that DB2, MS SQL, and Oracle are excluded options for non-technical reasons when MySQL and/or Postgres are under considerations.

Isn't a CTE in Postgres (unlike in MS SQL, AFAIR) also an optimization fence?

Just something to keep in mind when using it as a substitute for subquery, readability vs performance and all that :)

Yes, at present, the query executor can't optimize across CTEs. Sometimes, that's even the behavior you want.

> Sometimes, that's even the behavior you want.

Sadly, in most cases, this mean you have to decide between ugly and performant, or nice and slow code.

I hope this gets fixed soon. Nobody should have to write queries like this:

      select blah blah blah
      from x, (select blah blah blah
        from y, (select blah blah blah 
          from z, (select blah blah
            from w
            where a=b
            and c=d)
          where z.id = w.id
          and p = 2
          and q = 4)
        where z.id = y.different_id
        and r = 3
        and t = 'BLAH'
        and u not in (select u from w)) l
      where l.id = x.id

CTEs allow you to build "lisp-like" pipeline where you transform your data as you go and are able to give the intermediate results useful names.

Of course, you can first write the query with CTEs, then convert to the nested form. It's just substitution.

(For bonus points, keep the original CTE form, but commented out, to help with troubleshooting further down the road.)

As far as I know generally it doesn't matter. But please proof me wrong, sounds important!

Edit: It matters! See comment above :-)

http://cramer.io/2010/05/30/scaling-threaded-comments-on-dja... This is where recursions is useful. For many scenarios adjacency list + recursive CTE scales better than any other hierarchy model.

Do you mean Postgres's implementation of Excel pivot tables? ;)

tablefunc() :-)

Cool.