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
SELECT
from a table in Postgres all rows that match both the letter and the number, according to my collection.

The
IN
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 * FROM (
  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))

Performance

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