CYBERTEC PostgreSQL Logo

Fed up with long WHERE-clauses?

12.2013 / Category: / Tags:

SQL is a very easy to use language and it is pretty easy to make things work out of the box. In fact, from my point of view simplicity is one of the core advantages of SQL in general. But, what if you want to compare dozens of columns with each other? It is not hard to do but it can be a fair amount of unnecessary typing. 

A lengthy example

Let us create a table featuring 4 columns:

To do the test we can add a simple row:

Now we want to see all

This is simple, yet pretty lengthy. The point is: When you are about to write a query you want a result – not a typing exercise.

Simplified filtering

To make this entire filtering process a little easier, we can use brackets:

From my point of view this is ways more readable and a lot faster to write.
But, there is one more to do this:

Remember, in PostgreSQL every table can be seen as a composite type. So, this means that we can use the name of the table inside the WHERE clause directly. PostgreSQL will compare one field after the other and see if they are all equal. This is an even shorter version to compare those fields with each other.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.

4 responses to “Fed up with long WHERE-clauses?”

  1. Yay, relying on one of the great SQL mistakes, significant column order, to "simplify" WHERE clauses. It will also break after any add or drop column ddl to that table. Should have just leave it at (field list) = (values)

    • Given that this mistake is unlikely ever to be corrected, it's probably OK to rely on it for one-off queries. Your suggested method works much better for tables whose structure can change, as it will alert with a useful error when they do.

Leave a Reply

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

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram