Question 89851 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres returning records when query result should be empty – Postgresql

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 [].

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search