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

Re: count(*) of zero rows returns 1

From: David Johnston <polobo(at)yahoo(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: count(*) of zero rows returns 1
Date: 2013-01-14 20:09:59
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Tom Lane-2 wrote
> Gurjeet Singh &lt;

> singh.gurjeet@

> &gt; writes:
>> Can somebody explain why a standalone count(*) returns 1?
>> postgres=# select count(*);
>>  count
>> -------
>>      1
>> (1 row)
> The Oracle equivalent of that would be "SELECT count(*) FROM dual".
> Does it make more sense to you thought of that way?
>> I agree it's an odd thing for someone to query, but I feel it should
>> return
>> 0, and not 1.
> 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.  Another counterexample is
> regression=# select sum(42);
>  sum 
> -----
>   42
> (1 row)
> which by your argument would need to return NULL, since that would be
> SUM's result over zero rows.

Given that:


Results in: 

SQL Error: ERROR:  SELECT * with no tables specified is not valid

then an aggregate over an error should not magically cause the error to go

I am curious on some points:

Is there something in the standard that makes "SELECT count(*)" valid?
What does "SELECT * FROM dual" in Oracle yield?
Is there a meaningful use case for "SELECT sum(42)", or more specifically
any aggregate query where there are no table/value inputs?  

I get the "SELECT 2+2" and its ilk as there needs to be some way to evaluate

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(*)".

David J.

View this message in context:
Sent from the PostgreSQL - hackers mailing list archive at

In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2013-01-14 20:14:41
Subject: Re: Validation in to_date()
Previous:From: Simon RiggsDate: 2013-01-14 19:50:43
Subject: Re: Hash twice

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