Select rows where an postgresql array does not contain a certain value, using WHERE clause?

Question

In the following example the column parents is an array of int (parents INT[]).

If I want to select all rows in which the array of parents contains 42, I can run this query:

SELECT * FROM records WHERE 42 = ANY (parents);

Now I want to do the opposite and select each row in which parents does not contain 42.

I tried this:

SELECT * FROM records WHERE 42 != ANY (parents);

without luck... Does anyone know how this can be done?

I'm using Postgresql 10.3.

Jay Shankar Gupta · Accepted Answer · 2018-04-13 10:27:16Z

3

SELECT * FROM records WHERE NOT (42 = ANY (parents))

Demo

http://sqlfiddle.com/#!17/394c0/1

answered Apr 13, 2018 at 10:27

Jay Shankar Gupta

6,1281 gold badge14 silver badges31 bronze badges

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

Comments

M Danish · Accepted Answer · 2018-04-13 10:35:31Z

0

check example. Reference -> Refrence link

SELECT value_variable = ANY ('{1,2,3}'::int[])

answered Apr 13, 2018 at 10:35

M Danish

4782 silver badges5 bronze badges