How to make a select with array contains value clause in psql

Question

I have column arr which is of type array.

I need to get rows, where arr column contains value s

This query:

SELECT * FROM table WHERE arr @> ARRAY['s']

gives the error:

ERROR: operator does not exist: character varying[] @> text[]

Why does it not work?

p.s. I know about any() operator, but why doesn't @> work?

score 157 · Accepted Answer · edited May 17 '13 at 10:21

157

Try

SELECT * FROM table WHERE arr @> ARRAY['s']::varchar[]

edited May 17 '13 at 10:21

answered May 17 '13 at 10:16

Wojtas

2

Docs: https://www.postgresql.org/docs/9.1/functions-array.html – Gebb May 14 '20 at 11:11

score 92 · Answer 2 · answered May 04 '17 at 01:02

92

Note that this may also work:

SELECT * FROM table WHERE s=ANY(array)

answered May 04 '17 at 01:02

AetherUnbound

4

And this also prevents me from needing to cast to varchar, as in `s @> ARRAY['constant'::varchar]`, shorter. – Andrew Jul 04 '17 at 15:34
This won't use the GIN index so I wouldst use it. Correct me if I am wrong – GorillaApe Jan 29 '19 at 08:04
Worked for me in PostgreSQL 14. – Thilina Koggalage Jun 09 '22 at 05:05

score 20 · Answer 3 · answered Apr 30 '14 at 21:25

20

SELECT * FROM table WHERE arr && '{s}'::text[];

Compare two arrays for containment.

answered Apr 30 '14 at 21:25

vol7ron

3 Answers3