Query multiple columns on a given value in postgresql

Question

I have a table in my database (postgres) Table #1

----------------------------------------------------    
| name  |  id_a |  id_b | id_c | id_d |  id_e | id_f    
----------------------------------------------------

I am now given a list of ids. Some of these ids belong to id_b, some to id_c and so on till id_f. For these list of ids I need to find the corresponding id_a.

Method #1 Identify which ids belong to id_b, id_c and so on. Then use multiple in clause in my mysql-query to fetch id_a

select id_a from Table #1 t1 where t1.id_b in () or t1.id_c in ().... or t1.id_f in ();

I am looking for an alternative way.

Method #2

Is there some way to query all the columns simultaneously for a given value. Something like

select id_a from Table #1 t1 where t1.id_b,t1.id_c,..t1.id_f in ();

Braiam · Accepted Answer · 2021-10-18 14:42:36Z

6

In postgres, you can use the array-overlap, &&, operator:

SELECT id_a
FROM   mytable
WHERE  ARRAY[id_b, id_c, id_d, id_e, id_f] && ARRAY[value1, value2, ...]

edited Oct 18, 2021 at 14:42

Braiam

4,49511 gold badges50 silver badges83 bronze badges

answered Sep 19, 2018 at 7:11

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

1 Comment

mirabilos May 25 at 19:06

ouch… I just tried, I had to use WHERE ARRAY[a::text, b::text] && ARRAY['foo', 'bar'] (the casts), and it’s slow because it cannot use the indicēs on the columns

mateusppereira · Accepted Answer · 2021-07-28 11:05:56Z

4

Yesss, you can do it like

SELECT columnA
FROM tableA
WHERE (columnA, columnB, columnC)::text IN ('TEST', 'TEST2');

The cast ::text may be required

edited Jul 28, 2021 at 11:05

answered Aug 23, 2019 at 21:49

mateusppereira

9608 silver badges19 bronze badges

1 Comment

ricks Over a year ago

(columnA, columnB, columnC)::text groups the columns together so your output looks like valueA,valueB,valueC if you want to make this work it needs to be LIKE ('%TEST%') not sure performance vs an alternative solution

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-19 07:11:24Z

1

you can use union

select * from t where id_a in (

select * from 
(select id_b  from t union
select id_c from t union
select id_d from t union
select id_e from t union
select id_f from t
 ) as t1
)

answered Sep 19, 2018 at 7:11

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Query multiple columns on a given value in postgresql

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related