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.

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
10 years ago

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)

David Fetter
David Fetter
10 years ago
Reply to  teh

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.

10 years ago
Reply to  teh

Agreed, that last one is scary. I would never let that get into production code. Don't do this at home kids!

Luca Bruno
Luca Bruno
10 years ago

Would that also work for inserts?

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

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

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

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram