How to pass a integer array to not in conditions in PostgreSQL?

Question

I have the following case in PostgreSQL:

    xfields smallint[];
    xfields={1,2,3}

   select fields from table where fieldno not in (xfields);

if i execute the above query it shows error as " operator does not exist: smallint <> smallint[] "

can anyone help me how to pass the values in array in the above query or how to execute the above conditions?

Ansuman Bebarta · Accepted Answer · 2013-09-05 12:23:30Z

2

You can use array in query by using ALL or ANY functions.

For above query you can use

select fields from table where fieldno <> ALL(xfields);

user330315

answered Sep 5, 2013 at 11:07

Ansuman Bebarta

7,3366 gold badges29 silver badges44 bronze badges

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

2 Comments

please don't give links to old versions, OP uses 9.1, your link is to 8.2

@Roman: Thanks for the tip. I was trying to answer as quick as possible. My +1 goes to you for fiddle demo.

roman · Accepted Answer · 2013-09-05 11:11:27Z

2

select fields
from table
where fieldno <> all(xfields);

answered Sep 5, 2013 at 11:05

roman

118k30 gold badges205 silver badges209 bronze badges