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

krokodilko
– krokodilko

2020-06-19 05:26:38 +00:00
Commented Jun 19, 2020 at 5:26 — krokodilko
– krokodilko, Commented Jun 19, 2020 at 5:26

S.K · Accepted Answer · 2020-06-19 05:29:39Z

7

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, 2020 at 5:29

S.K

5401 gold badge5 silver badges21 bronze badges

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

Comments

Pavel Stehule · Accepted Answer · 2020-06-19 05:33:01Z

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.

Collectives™ on Stack Overflow

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

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