SQL IN operator in the 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]
 )
)

Doesn't work. I get the error:

"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?

codaddict · Accepted Answer · 2010-10-29 07:35:14Z

5

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)
 )
)

answered Oct 29, 2010 at 7:35

codaddict

457k83 gold badges502 silver badges537 bronze badges

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

Comments

Quassnoi · Accepted Answer · 2010-10-29 09:34:02Z

1

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 the array as a single bound variable or a parameter of a function:

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';

answered Oct 29, 2010 at 9:34

Quassnoi

428k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

SQL IN operator in the WHERE clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related