英文:
Postgres returning records when query result should be empty
问题
假设以下内容,
```sql
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}');
当我查询一个不存在的聊天记录的用户时,我仍然收到结果:
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
显然,没有与 chat_id_value = 1234
的 my_schema.chat
记录。
我尝试添加,
. . .
FROM my_schema.user as u
WHERE chat_ids != '{}'
但这仍然产生相同的结果:
[
{
"id": 1,
"chat_ids": []
}
]
我尝试 WHERE ARRAY_LENGTH(chat_ids, 1) != 0
,WHERE CARDINALITY(chat_ids) != 0
,都没有返回预期的结果。
奇怪的是,WHERE ARRAY_LENGTH(chat_ids, 1) != 1
可以工作,这意味着 chat_ids
的长度为 1
,而实际上它是 0
?非常令人困惑。
我在这里做错了什么?预期的结果应该是 []
。
<details>
<summary>英文:</summary>
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 `[]`.
</details>
# 答案1
**得分**: 3
如果在 `my_schema.chat` 上的子查询没有返回结果,你将会得到 NULL,而 `coalesce` 会将其转换为 `{}`。此外,内部查询与外部查询没有关联,因此在 `my_schema."user"` 的每一行中都会得到相同的结果。你应该使用内连接:
```sql
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);
我认为你的数据模型不太好。你应该避免使用数组,而是使用联接表。这将提供更好的性能和更简单的查询。
英文:
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.
答案2
得分: 1
你可以按以下方式操作:
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;
这将在查询不匹配时强制不显示任何结果!
英文:
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 !
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论