Posts Tagged "CTE"

The WITHIN GROUP and FILTER SQL clauses of PostgreSQL 9.4

PostgreSQL 9.4 extends the SQL standard by inserting two new clauses that facilitate many operations required during the development of applications: the WITHIN GROUP and FILTER clauses.

PostgreSQL’s CTEs are optimisation fences

As part of AXLE project work I recently found myself re-examining issues around optimisation fencing and non-semantic query execution changes in PostgreSQL. One key issue was the inability to use a CTE where optimisation (inlining, push-up/pull-down, flattening, etc) was desired. I’d like to explain that in more detail, as it’s something that can surprise new…

CTE and the Birthday Paradox

An interesting query has been twitted by Will Leinweber from Postgres Open: — this returns a different result each time it is ran with recursive s as ( select random() union select random() from s ) select count(*) from s; I like this example: a surprising result, which can be explained by (and indeed helps to explain) CTE…