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:
|
1 2 |
test=# CREATE TABLE t_test (a int, b int, c int, d int); CREATE TABLE |
To do the test we can add a simple row:
|
1 2 |
test=# INSERT INTO t_test VALUES (1, 2, 3, 4); INSERT 0 1 |
Now we want to see all
|
1 2 3 4 5 6 |
test=# SELECT * FROM t_test WHERE a = 1 AND b = 2 AND c = 3 AND d = 4; a | b | c | d ---+---+---+--- 1 | 2 | 3 | 4 (1 row) |
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:
|
1 2 3 4 5 6 |
test=# SELECT * FROM t_test WHERE (a, b, c, d) = (1, 2, 3, 4); a | b | c | d ---+---+---+--- 1 | 2 | 3 | 4 (1 row) |
From my point of view this is ways more readable and a lot faster to write.
But, there is one more to do this:
|
1 2 3 4 5 6 |
test=# SELECT * FROM t_test WHERE t_test = (1, 2, 3, 4); a | b | c | d ---+---+---+--- 1 | 2 | 3 | 4 (1 row) |
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.
+43 (0) 2622 93022-0
office@cybertec.at
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.
Agreed, that last one is scary. I would never let that get into production code. Don't do this at home kids!
Would that also work for inserts?