fiatjaf fiatjaf - 1 year ago 44
SQL Question

How to select rows matching multiple columns from a list of tuples?

For example, I have a collection with the following values:

{number: 7, letter: 'T'},
{number: 2, letter: 'R'},
{number: 4, letter: 'T'}

and a table with the columns

  • number

  • letter

I want to
from a table in Postgres all rows that match both the letter and the number, according to my collection.

query seems to be the tool for the job, but how can I make it work for tuples matching?

(I'm calling the pair of
(number, letter)
a tuple in this case)

Answer Source

Ok, I've searched everywhere and found no answer, maybe it is because I was searching with the wrong terms. But then I discovered the solution myself:

1) Rude solution: turn your values into strings.

  SELECT *, letter || '-' || number AS tuple FROM table
) WHERE tuple IN ('T-7', 'R-2', 'T-4')

2) Beautiful solution: just use tuples.

SELECT * FROM table)
WHERE (letter, number) IN (('T', 7), ('R', 2), ('T', 4))


The second one seems much better, and probably is (this "Performance" section is just a waste of time, you know that), but it has shown no performance advantage in my stupid benchmarks with a small table and 4 tuples to fetch. EXPLAIN ANALYSE outputs follow (not the same queries of the above section):

1) Rude:

Seq Scan on test  (cost=0.00..3.20 rows=4 width=11) (actual time=0.037..0.156 rows=4 loops=1)
   Filter: (((name || '-'::text) || (seq)::text) = ANY ('{maria-17,antônia-33,joana-64,joana-76}'::text[]))
   Rows Removed by Filter: 194
 Total runtime: 0.183 ms

2) Beautiful:

Seq Scan on test  (cost=0.00..3.39 rows=1 width=11) (actual time=0.022..0.077 rows=4 loops=1)
   Filter: (((name = 'maria'::text) AND (seq = 17)) OR ((name = 'antônia'::text) AND (seq = 33)) OR ((name = 'joana'::text) AND (seq = 64)) OR ((name = 'joana'::text) AND (seq = 76)))
   Rows Removed by Filter: 194
 Total runtime: 0.101 ms
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download