PostgreSQL multi-column WHERE = ANY(ARRAY)

Question

I have a table with three columns, "id", "letter" and "number". I have a list of pairs of "letter" and "number", for which I need to get the "id"s in single query. Obviously, the easy solution is to use n queries, where n is the size of the list.

SELECT id FROM table WHERE number=... AND letter=...

But that requires n queries, in my case it is millions and there is large overhead. Previously, I only had a filter on list of "number"s, so I used

SELECT id FROM table WHERE number = ANY(ARRAY[...])

Is there some syntax that would do what I need, something like

SELECT id FROM table WHERE PAIR[letter,number] = ANY(ARRAY[PAIR[...],...])

Thank you.

mu is too short · Accepted Answer · 2019-03-06 18:18:47Z

5

You could use an array of rows:

select id from table where (letter, number) = any(array[(l1, n1), (l2, n2), ...])

if you're set on using = any. You might need to include a lot of type casting to make sure everything lines up so it could be uglier than joining to a VALUES list.

answered Mar 6, 2019 at 18:18

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Radek Svoboda Over a year ago

This does not work, however, the issue might be that the second row is actually "character varying". I'm getting ERROR: cannot compare dissimilar column types character varying and unknown at record column 2

mu is too short Over a year ago

Hence the "You might need to include a lot of type casting to make sure everything lines up" note at the end. You might need to say things like (l1::varchar, n1::int) to get the types right.

isapir · Accepted Answer · 2019-03-06 19:58:25Z

3

While you can certainly use an ARRAY with the <@ (is-contained-by) operator or the ANY operator, you don't need arrays for that. A JOIN on fixed values will most certainly run much faster than the other options.

Consider the following test table with data:

CREATE TEMP TABLE IF NOT EXISTS 
    test(id SERIAL, letter TEXT, num NUMERIC);

WITH letters AS (
    SELECT chr(generate_series(65, 90)) AS letter
)
,numbers AS (
    SELECT generate_series(101, 999) AS num 
)
INSERT INTO test(letter, num)
SELECT  letter, num 
FROM    letters, numbers

You can now add your values to a query and do a JOIN. For example, the following query finds the id of the pairs ('A', 105), ('B', 110), and ('C', 879):

SELECT  id
FROM    test T
    JOIN (VALUES    /* your query criteria goes here */
        ('A', 105)
       ,('B', 110)
       ,('C', 879)
    ) AS V(l, n) 
        ON T.letter = V.l AND T.num = V.n

Which returns (assuming no prior writes were made to the test table):

id  |
----|
   5|
 909|
2577|

edited Mar 6, 2019 at 19:58

answered Mar 6, 2019 at 18:09

isapir

24.1k16 gold badges127 silver badges123 bronze badges

1 Comment

Radek Svoboda Over a year ago

Thanks, this works. I have finally discovered SELECT id FROM table WHERE (number, letter) = ANY (VALUES(1, 'A'), (2, 'B'), (3, 'C')) which seems the shortest.

Collectives™ on Stack Overflow

PostgreSQL multi-column WHERE = ANY(ARRAY)

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related