Why PostgreSQL is so Awesome
Why PostgreSQL is so Awesome
Join the DZone community and get the full member experience.Join For Free
Secure your Java app or API service quickly and easily with Okta's user authentication and authorization libraries. Developer accounts are free forever. Try Okta Instead.
Just recently, I blogged about PostgreSQL 9.3 having been released, which is awesome enough since PostgreSQL finally supports materialized and updatable views. I then blogged about PostgreSQL’s syntax being a mystery only exceeded by its power, as it allows for treating INSERT and UPDATE statements as table references, when used with the RETURNING clause. This is quite fancy, even if not very useful in everyday SQL.
But what I’m writing about today is colossal:
PostgreSQL Predicates are Just Ordinary Expressions
Let this sink in. To PostgreSQL, predicates are just ordinary expressions evaluating to the Boolean type. This is documented in the SELECT syntax reference, here:
The optional WHERE clause has the general form:WHERE condition
Where condition is any expression that evaluates to a result of type Boolean [...]
It struck me like lightning when I read this Stack Overflow question. You can put a predicate everywhere! Although I knew this before, I had never thought about how awesome this is! You can use predicates in the SELECT clause:
SELECT a, b, c = d, e IN (SELECT x FROM y) FROM t
You can use predicates in the GROUP BY clause:
SELECT count(*) FROM t GROUP BY c = d, e IN (SELECT x FROM y)
You can use predicates in the ORDER BY clause:
SELECT * FROM t ORDER BY c = d, e IN (SELECT x FROM y)
You can aggregate predicates using the EVERY aggregate function.
Don’t believe it? See for yourself in this SQLFiddle!
In “ordinary” SQL (i.e., standards-compliant), predicates have to be transformed into ordinary value expressions using the CASE clause. Repeating the above examples:
SELECT a, b, CASE WHEN c = d THEN true ELSE false END, CASE WHEN e IN (SELECT x FROM y) THEN true ELSE false END FROM t
GROUP BY clause:
SELECT count(*) FROM t GROUP BY CASE WHEN c = d THEN true ELSE false END, CASE WHEN e IN (SELECT x FROM y) THEN true ELSE false END
ORDER BY clause:
SELECT * FROM t ORDER BY CASE WHEN c = d THEN true ELSE false END, CASE WHEN e IN (SELECT x FROM y) THEN true ELSE false END
Impact for jOOQ
The answer given in the above Stack Overflow question shows how jOOQ can standardize this behavior by rendering predicates and conditions directly as column expressions where this is supported, while emulating this behavior using an equivalent CASE expression, otherwise.
Take predicates in the SELECT clause, for instance:
DSL.using(configuration) .select( T.A, T.B, // Transform a jOOQ Condition into a Field: field(T.C.eq(T.D)), field(T.E.in(select(Y.X).from(Y))) ) .from(T);
From jOOQ integration test experience, I can say that Derby, H2, HSQLDB, MariaDB, MySQL, PostgreSQL, and SQLite will work this way. While this is awesome from a syntax perspective, do keep an eye on your execution plans to verify that this isn’t going to produce lots of very expensive nested loops …
Published at DZone with permission of Lukas Eder , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.