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

Question

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 1

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 

Answer 2

Also check out this How can I rewrite a multi-column IN clause to work on SQLite? solution. I used it to do the same thing in sqlite, which does not support using IN for a list of tuples.

Essentially, you create a temporary table of the pairs you want to match and then do a join select between that table and your table of interest.

Create the temporary table of pairs you're interested in checking for, as tuples:

CREATE TEMPORARY TABLE pair (_number INTEGER, _letter TEXT);         INSERT INTO pair (_number, _letter) VALUES ('2', 'R');         INSERT INTO pair (_number, _letter) VALUES ('4', 'B');         INSERT INTO pair (_number, _letter) VALUES ('7', 'R'); 

Then do the SELECT query to find the values in the original data table:

SELECT     my_table.* FROM       my_table INNER JOIN  pair         ON pair._number = my_table.number        AND pair._letter = my_table.letter; 

The result is this one -> ('2', 'R')

Fiddle example here: http://sqlfiddle.com/#!17/6f045/3

There are distinctions between the different cases for when your pairs are unique, not unique, and are not in the table.