Home » Software Development » Why PostgreSQL is so Awesome

About Lukas Eder

Lukas is a Java and SQL enthusiast developer. He created the Data Geekery GmbH. He is the creator of jOOQ, a comprehensive SQL library for Java, and he is blogging mostly about these three topics: Java, SQL and jOOQ.

Why PostgreSQL is so Awesome

Just recently, I’ve blogged about PostgreSQL 9.3 having been released, which is awesome enough as PostgreSQL finally supports materialised views and updatable views. I have 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: http://www.postgresql.org/docs/9.3/static/sql-select.html

Citing:

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!

“Ordinary” SQL

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 clause:

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 standardise this behaviour by rendering predicates / conditions directly as column expressions where this is supported, while emulating this behaviour 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);

Further thoughts

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…
 

Reference: Why PostgreSQL is so Awesome from our JCG partner Lukas Eder at the JAVA, SQL, AND JOOQ blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*


9 − = five

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Do you want to know how to develop your skillset and become a ...

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!
Get ready to Rock!
To download the books, please verify your email address by following the instructions found on the email we just sent you.

THANK YOU!

Close