使用WHERE子句选择postgresql数组不包含特定值的行？(Select rows where an postgresql array does not contain a certain va

使用WHERE子句选择postgresql数组不包含特定值的行？(Select rows where an postgresql array does not contain a certain value, using WHERE clause?)

在以下示例中， parents列是int array （ parents INT[] ）。

如果我想选择parents数组中包含42所有行，则可以运行此查询：

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

现在我想做相反的事情，并选择parents不包含的每一行42 。

我试过这个：

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

没有运气......有人知道这可以做到吗？

我正在使用Postgresql 10.3 。

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.

最满意答案

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

演示

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

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

Demo

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