How do I pass an array to where clause in PostgreSQL?

Question

I'm trying to delete from a table using where clause while passing it an array.

DELETE FROM Btemp
WHERE col2 =
    (SELECT ARRAY((SELECT col2 FROM Atemp INTERSECT SELECT col2 FROM Btemp)));

I'm getting this error:

ERROR:  operator does not exist: character varying = character varying[]
LINE 2: WHERE col2 =
                   ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883

And if do it without array, like below

DELETE FROM Btemp
WHERE col2 =
    (SELECT col2 FROM Atemp INTERSECT SELECT col2 FROM Btemp);

I get error:

ERROR:  more than one row returned by a subquery used as an expression

What should I do?

Tip: WRITING IN ALL CAPITALS is often used to represent SHOUTING, so may be considered rude. — IMSoP, Jan 05 '22 at 11:12
Data types are essential. You cannot compare a scalar (individual) to a collection (array/ population). (you *could* try to find out if it is *part* of that collection) — wildplasser, Jan 05 '22 at 11:19

score 0 · Answer 1 · answered Jan 05 '22 at 15:11

0

DELETE FROM Btemp
WHERE col2 =
    ANY(SELECT col2 FROM Atemp INTERSECT SELECT col2 FROM Btemp);

further reference: IN vs ANY operator in PostgreSQL

answered Jan 05 '22 at 15:11

jian

4,119
1
17
32

How do I pass an array to where clause in PostgreSQL?

1 Answers1