Check that multiple values do not exist in array column?

Question

I'm trying to build a query that returns records that have a character varying[] column that does not contain multiple values? Something like:

WHERE {char1,char2,char3} != ALL(char_array_col)

Any help appreciated, thank you.

GMB · Accepted Answer · 2020-12-08 19:24:00Z

1

If you have your multiple values in an array, you can do:

where not myarray  @> array['val1', 'val2', 'val3']

This ensures that myarray does not contain all of the values.

On the other hand, if you want to ensure that myarray does not contain any of the values, use overlap operator &&:

where not myarray  && array['val1', 'val2', 'val3']

answered Dec 8, 2020 at 19:24

GMB

224k25 gold badges103 silver badges151 bronze badges

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

5 Comments

tom_nb_ny Over a year ago

Thanks - unfortunately I get syntax errors when trying either of those. The latter one is the one I need. "No operator matches the given name and argument types. You might need to add explicit type casts."

GMB Over a year ago

@tom_nb_ny: so just cast: where not myarray && array['val1', 'val2', 'val3']::character varying[]

GMB Over a year ago

@tom_nb_ny: please share the entire error message.

tom_nb_ny Over a year ago

I fiddled around and got it to execute - pet IS NULL OR NOT pet && '{cat,dog}' worked. Thanks for your help.

GMB Over a year ago

@tom_nb_ny: mm ok, so that's implicit conversion of the string on the left side of the operator to an array. Fine.

Collectives™ on Stack Overflow

Check that multiple values do not exist in array column?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related