0

I'd like to do something like this in postgres

select * from table t where t.one = 11 AND t.two = 12 and t.three = 13
union all
select * from table t where t.one = 21 AND t.two = 22 and t.three = 23

I tried join lateral and inner joins but the performance is too bad. So I need to union all these queries but I don't want just to concat an indefinate amount of these values, Is there something like these https://stackoverflow.com/a/55484168/1321514 for postgres?

pablo
  • 747
  • 1
  • 10
  • 24
  • What do you mean by "indefinate amount of these values"? Which values, where do they come from, and in which ways can they vary? Maybe you should show us your join attempts if they do what you want. – Bergi Oct 01 '19 at 17:01
  • 1
    Yes, PostgreSQL has standard `unnest`. Did you try to use it? – Bergi Oct 01 '19 at 17:02

1 Answers1

4

I don't see the need for a UNION at all. And I don't understand how a JOIN would help here

Your query is equivalent to:

select * 
from table t 
where (t.one,t.two,t.three) in ( (11,12,13), (21,22,23) );

Alternatively you can try joining to a VALUES clause:

select t.* 
from table t 
  join (
     values (11,12,13), (21,22,23) 
  ) as x(c1,c2,c3) on t.one = x.c1 
                  and t.two = x.c2
                  and t.three = x.c3;
  • thanks a lot @a_horse_with_no_name, that was exactly what I was looking for – pablo Oct 01 '19 at 18:16
  • is it possible to do this using java with jdbc? thanks – pablo Oct 03 '19 at 09:05
  • @otonakav: yes, of course. Just run that SQL through `Statement.executeQuery()` –  Oct 03 '19 at 09:06
  • I mean passing the array containing the values for column `one`, another array for column `two` and another array for column `three`, I was trying to do `select * from table where (one, two, three) in (select * from unnest(array[11,21]::integer[], array[12,22]::integer[], array[13,23]::integer[]))` and then in java setting the arrays with `setArrayOf()` but this destroys the performance @a_horse_with_no_name – pablo Oct 03 '19 at 09:12
  • 1
    Please ask a new question for that. –  Oct 03 '19 at 10:04
  • https://stackoverflow.com/questions/58217068/pass-unknown-number-of-parameters-to-in-clause-using-jdbc-and-postgres – pablo Oct 03 '19 at 10:20