In clause

[glennpierce]

Is it possible to do a In clause with a vector. In my case i32's

ie

let results : postgres::Result = self.conn.query("SELECT id, name FROM sensors WHERE id IN {}", &[&sensor_ids]);

I seem to get syntax error at or near "{

Thanks

[sfackler]

You can use the ANY function:

SELECT id, name FROM sensors WHERE id = ANY($1)

[glennpierce]

Thanks.
Is there a document that explains where variables can be substituted ?

Ie I have this query ported from python / postgres

                sql = r#"SELECT * FROM crosstab($$
                                SELECT ts::date, ARRAY[1,s1.sensor_id], AVG(s1.value)
                                  FROM sensor_values s1
                                   WHERE s1.ts::timestamptz >= $1::timestamptz AND
                                         s1.ts::timestamptz <= $2::timestamptz
                                         AND s1.sensor_id = ANY($3)
                                    GROUP BY s1.sensor_id, ts::date ORDER BY 1 ASC
                                 $$,
                                 $$VALUES (ARRAY[1, 597992]),(ARRAY[2, 597992]),(ARRAY[1, 610978]),(ARRAY[2, 610978]),(ARRAY[1, 597998]),(ARRAY[2, 597998]) $$)
                                 AS ct (time timestamptz, "bi1600068_import_(597992)" float,"bi1700063_import_(610978)" float,"bi1600070_import_(597998)" float )"#.to_string();
            }

            let results : postgres::Result<Rows> = self.conn.query(&sql, &[&start_timestamp, &end_timestamp, &sensor_ids]);

This results in

panicked at 'expected 0 parameters but got 3'
Looks like $1, $2 and $3 are not seen ?

Do I have to build more advanced queries with format! ?

[sfackler]

The Postgres documentation is the place to look. Variables can only be substituted in the place of values. The IN clause doesn't take a single array value, but instead a comma-separated list of values in parentheses: https://www.postgresql.org/docs/10/static/functions-comparisons.html#FUNCTIONS-COMPARISONS-IN-SCALAR.

The parameters in that query are inside of an anonymous function, and are treated as the arguments to the function rather than parameters for the query itself. It probably worked in Python because it does parameter substitution by directly rewriting the query string rather than relying on Postgres's support for parameters.