How to extract only rows matching condition ‘WHERE column1 IN column2’ (where column2 is an array)?

I am using postgresql.

Let’s suppose I have this table name my_table:

  id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   1 | 20447 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   3 | 20478 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   4 | 20482 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |

I want to select only the rows having the value of idm equal to one of the elements of the array in stores (of that line).

So I want to get something like this:

  id | idcm |  stores |     du     |     au     |              dtc              | 
  ----------------------------------------------------------------------------------
   2 | 20456 | [2, 5] | 2022-11-02 | 2022-11-15 | 2022-11-03 11:12:19.213799+01 | 
   5 | 20485 | [7, 5] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 | 
   6 | 20497 | [2, 6] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |
   7 | 20499 | [5, 7] | 2022-11-02 | 2022-11-15 | 2022-10-25 20:25:08.949996+02 |

I have tryed with

select * from my_table where id in stores;

but I get a syntax error.

Answer

You need to use the ANY() operator:

select * 
from my_table 
where id = any(stores);

Advertisement

Answer