PostgreSQL Magic

Post by greggyb » Fri, Sep 11, 2015, 3:21 PM UTC

jeltz wrote: post #37

greggyb wrote: post #29

Earlier quoted context omitted.

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.

Post by j_s » Fri, Sep 11, 2015, 4:07 PM UTC

greggyb wrote: post #22

timonv wrote: post #2

As a fellow Postgres amateur wizard, love the positive attention that postgres seems to be getting more and more, and some half databases less and less (unless you actually need map-reduce, ofcourse. You probably don't. /trollface) What I miss though in this article, and where I think postgres shines majorly compared to other rel dbs, are window functions. It allows you to apply a partition to a set. You can do some…

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 S…

Post by zrail » Fri, Sep 11, 2015, 4:17 PM UTC

Post by glogla » Fri, Sep 11, 2015, 5:28 PM UTC

rosser wrote: post #11

Hakeashar wrote: post #5

Earlier quoted context omitted.

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.

      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

Post by davidgerard » Fri, Sep 11, 2015, 6:22 PM UTC

crc32 wrote: post #23

greggyb wrote: post #22

Earlier quoted context omitted.

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 S…

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