Re: count(*) of zero rows returns 1
| От | Gurjeet Singh |
|---|---|
| Тема | Re: count(*) of zero rows returns 1 |
| Дата | |
| Msg-id | CABwTF4WRXsw6MXqLZ0Ax1x30jy+wY=BJ9XZ5D+j3uvOLtCc7OA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: count(*) of zero rows returns 1 (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: count(*) of zero rows returns 1
Re: count(*) of zero rows returns 1 Re: count(*) of zero rows returns 1 Re: count(*) of zero rows returns 1 |
| Список | pgsql-hackers |
On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Interesting to note that SELECT * FROM table_with_zero_cols does not complain of anything.
postgres=# select * from test1;
--
(0 rows)
This I believe result of the fact that we allow user to drop all columns of a table.
On a side note, Postgres allows me to do this (which I don't think is a bug or useless): I inserted some rows into a table, and then dropped the columns. The resulting table has no columns, but live rows.
postgres=# select * from test_0_col_table ;
--
(200000 rows)
I wasn't advocating it, but was trying to wrap my head around why Postgres would do something like count(*) of nothing == 1.
David Johnston <polobo@yahoo.com> writes:
> Tom Lane-2 wrote>> For that to return zero, it would also be necessary for "SELECT 2+2"
>> to return zero rows. Which would be consistent with some views of the
>> universe, but not particularly useful.> Given that:That has nothing to do with the number of rows, though. That's
> SELECT *;
> Results in:
> SQL Error: ERROR: SELECT * with no tables specified is not valid
complaining that there are no columns for the * to refer to.
Interesting to note that SELECT * FROM table_with_zero_cols does not complain of anything.
postgres=# select * from test1;
--
(0 rows)
This I believe result of the fact that we allow user to drop all columns of a table.
On a side note, Postgres allows me to do this (which I don't think is a bug or useless): I inserted some rows into a table, and then dropped the columns. The resulting table has no columns, but live rows.
postgres=# select * from test_0_col_table ;
--
(200000 rows)
> I get that the horse has already left the barn on this one but neither "0"Yeah, it's more about convenience than principle. AFAICS there are three
> nor "1" seem particularly sound answers to the question "SELECT count(*)".
defensible answers to what an omitted FROM clause ought to mean:
1. It's not legal (the SQL spec's answer).
2. It implicitly means a table of no columns and 1 row (PG's answer).
3. It implicitly means a table of no columns and 0 rows (which is what
I take Gurjeet to be advocating for).
I wasn't advocating it, but was trying to wrap my head around why Postgres would do something like count(*) of nothing == 1.
--
В списке pgsql-hackers по дате отправления: