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.
>Solution :
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.