Skip site navigation (1) Skip section navigation (2)

Re: count(*) of zero rows returns 1

From: Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Johnston <polobo(at)yahoo(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: count(*) of zero rows returns 1
Date: 2013-01-15 03:47:58
Message-ID: CABwTF4WRXsw6MXqLZ0Ax1x30jy+wY=BJ9XZ5D+j3uvOLtCc7OA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Jan 14, 2013 at 4:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Johnston <polobo(at)yahoo(dot)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/

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2013-01-15 04:00:00
Subject: Re: logical changeset generation v4
Previous:From: Tom LaneDate: 2013-01-15 03:33:45
Subject: Re: Patches for TODO item: Avoid truncating empty OCDR temp tables on COMMIT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group