On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> David Johnston <pol...@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: > > > SELECT *; > > Results in: > > SQL Error: ERROR: SELECT * with no tables specified is not valid > > That has nothing to do with the number of rows, though. That's > 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" > > nor "1" seem particularly sound answers to the question "SELECT > count(*)". > > Yeah, it's more about convenience than principle. AFAICS there are three > 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. -- Gurjeet Singh http://gurjeet.singh.im/
