英文:
Postgres empty SELECT clause "affects rows"
问题
I stumbled upon Postgres behaviour, which I do not understand. I enter this simple query. Please notice that there's no expression in SELECT clause:
我遇到了我不理解的Postgres行为。我输入了这个简单的查询。请注意SELECT子句中没有表达式：
SELECT FROM (VALUES (1), (2), (3)) AS data(value);
Server replies with
服务器回复：
Query executed OK, 3 rows affected.
查询执行成功，影响了3行。
According to the documentation for SELECT statement, the empty clause seems to be perfectly valid, but I can't understand, what affected rows mean in this case. EXPLAIN gives me simple "values scan" query plan. When I change SELECT to SELECT *, the query works as expected and returns table with three rows.
根据SELECT语句的文档，空子句似乎是完全有效的，但我不明白在这种情况下受影响的行是什么意思。EXPLAIN给我一个简单的“values scan”查询计划。当我将SELECT更改为SELECT *时，查询按预期工作，并返回包含三行的表。
What does that SELECT <nothing> FROM ... do?
My Postgres version is 9.5.
SELECT <nothing> FROM ... 是什么意思？
我的Postgres版本是9.5。
英文:
I stumbled upon Postgres behaviour, which I do not understand. I enter this simple query. Please notice that there's no expression in SELECT clause:
SELECT FROM (VALUES (1), (2), (3)) AS data(value);
Server replies with
Query executed OK, 3 rows affected.
According to the documentation for SELECT statement, the empty clause seems to be perfectly valid, but I can't understand, what affected rows mean in this case. EXPLAIN gives me simple "values scan" query plan. When I change SELECT to SELECT *, the query works as expected and returns table with three rows.
What does that SELECT <nothing> FROM ... do?
My Postgres version is 9.5.
答案1
得分: 1
PostgreSQL 允许空的 SELECT 列表（SQL 标准不允许）。
"3 rows affected" 必须是在接收到包含三行的结果集时，您的客户端才会显示的内容。 即使这些行不包含任何列，结果集确实包含三行。也许您的客户端将结果集显示为空，但实际上不是空的。
英文:
PostgreSQL allows empty SELECT lists (the SQL standard does not allow that).
"3 rows affected" must be something that your client says when it receives a result set with three rows. The result set does contain three rows, even if they contain no column. Perhaps your client displays the result set as if it were empty, but it isn't.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论