Postgres IN operator raises ERROR: syntax error at or near array_variable

Question

I am making function and one of my feature is make use of SQL IN operator. Basically I would like query like this

select name
from atlas_ins_th_travel_place pp
where pp.name IN ('Guyana', 'Mexico');

Then I make function and accept varchar[] as an input like this

CREATE OR REPLACE FUNCTION test_place(
    places VARCHAR[]
) RETURNS SETOF test_place_view AS
$$
DECLARE
    dummy ALIAS FOR $1;
BEGIN
--    FOR i IN 1 .. array_upper(places, 1)
--        LOOP
--           RAISE NOTICE '%', places[i];      -- single quotes!
--             array_append(dummy, places[i])
--        END LOOP;
    RETURN QUERY
    select name
    from atlas_ins_th_travel_place
    where name in places;
END;
$$ LANGUAGE plpgsql STABLE;

Unfortunately it raises the error regarding operator. I have tried making new array and use with it. But it does not help

Question:

How to use IN operator with array?

[PostgreSQL Array Functions and Operators](https://www.w3resource.com/PostgreSQL/postgresql-array-operators-and-functions.php) — krokodilko, Jun 19 '20 at 05:26

score 4 · Accepted Answer · answered Jun 19 '20 at 05:29

4

You need to use operator ANY for this. IN works with Lists, ANY works for Arrays select name from table where name = ANY(places)

answered Jun 19 '20 at 05:29

S.K

480
1
4
19

score 0 · Answer 2 · answered Jun 19 '20 at 05:33

Operator IN supports only lists. It doesn't support searching in a array. You should to use operator = ANY().

postgres=# select 10 = ANY(ARRAY[10,20,30]);
┌──────────┐
│ ?column? │
╞══════════╡
│ t        │
└──────────┘
(1 row)

postgres=# select 11 = ANY(ARRAY[10,20,30]);
┌──────────┐
│ ?column? │
╞══════════╡
│ f        │
└──────────┘
(1 row)

or maybe you can use a function array_position (but there a statistics will not be used):

postgres=# select array_position(ARRAY[10,2,3], 10);
┌────────────────┐
│ array_position │
╞════════════════╡
│              1 │
└────────────────┘
(1 row)

postgres=# select array_position(ARRAY[10,2,3], 11);
┌────────────────┐
│ array_position │
╞════════════════╡
│              ∅ │
└────────────────┘
(1 row)

If a array is small (< 100), then is not too important, what you use. For larger arrays probably better is using a operator. But it depends on the context.

Postgres IN operator raises ERROR: syntax error at or near array_variable

2 Answers2