Why does Postgres return nothing when testing a column against an empty array?

Question

Tried looking around online, couldn't find much on this.

Suppose the following:

create schema if not exists sb;

create table if not exists sb.test (id serial primary key);

insert into sb.test values (1), (2), (3);

select * from sb.test where id != any('{}'::int[])

Why is the resulting set empty?

If I do this, then it works as expected:

select * from sb.test where id != any('{}'::int[])

-- [ { "id": 2 }, { "id": 3 } ]

How can I get the first query to return all records?

Such a condition does not make sense to me. The values 1, 2, 3 you inserted in your table would all be returned by select [...] where id != any('{1, 2, 3}'::int[]) since 1 is different from at least one element in [1,2,3], same for 2 and same for 3; of course, they all are equal to at least one element in [1,2,3] too. — Atmo
– Atmo, Commented Nov 11, 2023 at 10:49
Such a comparison tends to return no records (empty array), all the records (several values in the array) and very occasionally 1 record (array with 1 element); that is a very convoluted way to achieve this behavior. I doubt that is what you want in the first place; What is it exactly that you are trying to do? — Atmo
– Atmo, Commented Nov 11, 2023 at 10:54
I'm trying to perform a search and, in the event that the result of that search yields a set of users with a length less than that of a specific size, then perform an "extended" search. The endpoint (and therefore query) is the same, which means that the first non-extended search would not be excluding any users. If the first search returns, say, 1 user due to other constraints (such as a specified radius, for example), then the extended search (with an increased radius) should fetch all other users EXCEPT the one already returned from the first search — Mike K
– Mike K, Commented Nov 11, 2023 at 10:55
As a sidenote, I've noticed that all instead of any yields the expected result (select * from sb.test where id != all('{}'::int[])). Is this the answer to my question, perhaps? — Mike K
– Mike K, Commented Nov 11, 2023 at 10:58
I'm trying to exclude the ids in that array, not return them (because the ids in that array are that of users that have already been fetched and rendered on the frontend) — Mike K
– Mike K, Commented Nov 11, 2023 at 11:01

Atmo · Accepted Answer · 2023-11-11 11:16:33Z

1

From your comments, what you want to do is get all the records from the table except from a list of ids you have already built elsewhere (e.g. in an application connected to your database.
I will demonstrate the possibilities you have with a non-empty array in parameter:

You can do it with an UNNEST:

SELECT * FROM test
WHERE id NOT IN (SELECT UNNEST(ARRAY[1, 2]))

Or with a sub-query (below, using VALUES):

SELECT * FROM test
WHERE id NOT IN (VALUES(1),(2))

Edit:

Like I said in comments, the below works too:

SELECT * FROM test
WHERE id != all(ARRAY[1,2])

Id 3 indeed is different from all the values in the array.

edited Nov 11, 2023 at 11:16

answered Nov 11, 2023 at 11:04

Atmo

4,2621 gold badge7 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Why does Postgres return nothing when testing a column against an empty array?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related