Postgres returning records when query result should be empty

Question

Suppose the following,

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.user (
    id serial PRIMARY KEY,
    chat_ids BIGINT[] NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.chat (
    id serial PRIMARY KEY,
    chat_id_value BIGINT UNIQUE NOT NULL
);

INSERT INTO my_schema.chat VALUES
    (1, 12321);

INSERT INTO my_schema.user VALUES
    (1, '{12321}');

When I query for a user record with a nonexisting chat, I still receive a result:

SELECT u.id,
    (
        SELECT TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
        FROM my_schema.chat as c
        WHERE c.chat_id_value = ANY (ARRAY[ 1234 ]::int[])
    ) AS chat_ids
FROM my_schema.user as u

Clearly, there is no my_schema.chat record with with chat_id_value = 1234.

I've tried adding,

. . .
FROM my_schema.user as u
WHERE chat_ids != '{}'

But this still yields the same result:

[
    {
        "id": 1,
        "chat_ids": []
    }
]

I've tried WHERE ARRAY_LENGTH(chat_ids, 1) != 0, WHERE CARDINALITY(chat_ids) != 0, none return the expected result.

Oddly enough, WHERE ARRAY_LENGTH(chat_ids, 1) != 1 works, implying the length of chat_ids is 1 when it's actually 0? Very confusing.

What am I doing wrong here? The expected result should be [].

You select all records from my_schema.user, and also try to create a json object. Where there is at least one record in this table, you get a result. — Frank Heikens
– Frank Heikens, Commented Feb 6, 2023 at 12:49
Correct, but then I specify WHERE chat_ids != '{}' and I still get the same result. — Mike K
– Mike K, Commented Feb 6, 2023 at 12:50
I've also tried WHERE CARDINALITY(chat_ids) != 0, but same issue — Mike K
– Mike K, Commented Feb 6, 2023 at 12:52
I don't get it. With your first query there is no result. Witch version are you using. Can you replicate on fiddle? db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/0 — user_0
– user_0, Commented Feb 6, 2023 at 13:08

Laurenz Albe · Accepted Answer · 2023-02-06 13:10:16Z

3

If the subselect on my_schema.chat returns no result, you will get NULL, which coalesce will turn into {}. Moreover, the inner query is not correlated to the outer query, so you will get the same result for each row in my_schema."user". You should use an inner join:

SELECT u.id,
       TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))
FROM my_schema.user as u
   JOIN my_schema.chat as c
      ON c.chat_id_value = ANY (u.chat_ids);

I don't think that your data model is good. You should avoid arrays and use a junction table instead. It will make for better performance and simpler queries.

answered Feb 6, 2023 at 13:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

4 Comments

Mike K Over a year ago

Thanks for your answer. Regarding your note, is this what you mean? dbfiddle.uk/sPyoAMmh

Laurenz Albe Over a year ago

Yes, except that the primary key for the junction table had best be on (user_id, chat_id).

Mike K Over a year ago

Is this correct? dbfiddle.uk/CMJDtpGC

Laurenz Albe Over a year ago

Yes, that's exactly what I would suggest. I recommend using bigint and never integer for autogenerated primary keys. Identity columns are the modern replacement for serial and bigserial.

SelVazi · Accepted Answer · 2023-02-06 13:23:56Z

1

You can do it as follows :

WITH cte as (
  SELECT TO_JSON(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL)) as to_json
  FROM my_schema.chat as c
  inner join  my_schema.user u on c.chat_id_value = ANY (u.chat_ids)
  WHERE c.chat_id_value = ANY (ARRAY[ 12321]::int[])
)
select * 
from cte where to_json is not null;

This will force not to show any result if the query don't match !

Demo here

answered Feb 6, 2023 at 13:23

SelVazi

16.2k2 gold badges19 silver badges35 bronze badges

Collectives™ on Stack Overflow

Postgres returning records when query result should be empty

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related