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: PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: count(*) of zero rows returns 1
Date: 2013-01-14 03:42:09
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Sun, Jan 13, 2013 at 4:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> 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?

For a user, Oracle's case makes perfect sense, since the command is
querying a single-row table. In Postgres' case, there's nothing being
queried, so the result's got to be either 0 or NULL.

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

Hmm..  Now that you put it that way, I agree it's a useful feature, or
shall I say, a quirk with useful side effect.

Gurjeet Singh

In response to

pgsql-hackers by date

Next:From: Amit kapilaDate: 2013-01-14 06:47:02
Subject: Re: Proposal for Allow postgresql.conf values to be changed via SQL [review]
Previous:From: Tom LaneDate: 2013-01-14 03:34:33

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