SQL IN statement in a WHERE clause

Question

The following query:

SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id IN ( ARRAY[29800001,29800008,29800015,29800022,29800029] ) ) 

Does not work. I get an error message:

"The operator does not exist: integer = integer []".

But

 SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id = 29800001 ) 

Works.

What am I doing wrong?

Answer 1

Try:

 SELECT SUM (peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id IN (29800001,29800008,29800015,29800022,29800029) ) ) 

Answer 2

Alternatively, use this syntax:

 SELECT SUM(peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id = ANY(ARRAY[29800001,29800008,29800015,29800022,29800029]) ) 

Useful if you pass an array as one related variable or function parameter:

 CREATE FUNCTION fn_peak_power (mpids INT[]) AS $$ SELECT SUM(peak_power) FROM t_mpi_pv WHERE info_id IN ( SELECT mp_info_id FROM t_mp WHERE mp_id = ANY($1) ); $$ LANGUAGE 'sql';