英文:
Postgres returning records when query result should be empty
问题
假设以下内容，```sqlCREATE 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 cWHERE c.chat_id_value = ANY (ARRAY[ 1234 ]::int[])) AS chat_idsFROM my_schema.user as u
显然，没有与 chat_id_value = 1234 的 my_schema.chat 记录。
我尝试添加，
. . .FROM my_schema.user as uWHERE 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"` 的每一行中都会得到相同的结果。你应该使用内连接：```sqlSELECT u.id,TO_JSON(COALESCE(ARRAY_AGG(c.*) FILTER (WHERE c IS NOT NULL), '{}'))FROM my_schema.user as uJOIN my_schema.chat as cON 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 uJOIN my_schema.chat as cON 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_jsonFROM my_schema.chat as cinner 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_jsonFROM my_schema.chat as cinner 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 !
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论