Обсуждение: select count(*);

Hello,


select *;
----------
ERROR:  SELECT * with no tables specified is not valid

select count(*);
----------------
1

Is this a must? and why 1?

It may lead to uncatched issues by typos. e.g.:

select count(*) FROMpg_stat_activity;

FROMpg_stat_activity
---------------------
1

regards,

Marc Mamin

> select *;
> ----------
> ERROR:  SELECT * with no tables specified is not valid
>
> select count(*);
> ----------------
> 1
>
> Is this a must? and why 1?

Hi,

regarding the "why 1" part:

I think that if we accept that

chris=> select 'foo';
 ?column?
----------
 foo
(1 row)

returns 1 row, then naturally

chris=> select count('foo');
 count
-------
     1
(1 row)

should give a count of 1...

The * might be a bit tricky, though,
since 'select *;' doesn't work.

Bye,
Chris.

> > select *;
> > ----------
> > ERROR:  SELECT * with no tables specified is not valid
> >
> > select count(*);
> > ----------------
> > 1
> >
> > Is this a must? and why 1?
>
> Hi,
>
> regarding the "why 1" part:
>
> I think that if we accept that
>
> chris=> select 'foo';
>  ?column?
> ----------
>  foo
> (1 row)
>
> returns 1 row, then naturally
>
> chris=> select count('foo');
>  count
> -------
>      1
> (1 row)
>
> should give a count of 1...
>
> The * might be a bit tricky, though,
> since 'select *;' doesn't work.

That's the point. * has no meaning without FROM

Marc

>>That's the point. * has no meaning without FROM


>But COUNT(*) 
>
>does have meaning - it means "the number of rows".

which rows? :-)

> It's not counting the number of columns in the row, so postgres doesn't need to know what columns exist in the row to
returna row count.
 

>Geoff

Marc Mamin <M.Mamin@intershop.de> writes:
>> The * might be a bit tricky, though,
>> since 'select *;' doesn't work.

> That's the point. * has no meaning without FROM

PG regards "count(*)" as a weird spelling of "count()", ie, invoke an
aggregate that takes no arguments.  It really doesn't have anything to do
with the meaning of "*" as a SELECT-list item, any more than it does with
the meaning of "*" as multiplication, say.  It's just syntax that's
written like that because the SQL standard says we have to.

Another way of making the point is that SELECT without a FROM list can
be seen as implicitly selecting from a dummy table with one row and no
columns.  Some other systems such as Oracle make you do that explicitly,
ie the infamous "FROM dual" hack; there's nothing in the SQL standard
saying you can omit FROM.  If you suppose that that's the underlying model
then the result of "select count(*)" is completely natural.

            regards, tom lane

On 2015-06-11 08:20, Geoff Winkless wrote:
> On 11 June 2015 at 15:17, Marc Mamin <M.Mamin@intershop.de
> <mailto:M.Mamin@intershop.de>>wrote:
>
>     >But COUNT(*)
>     >
>     >does have meaning - it means "the number of rows".
>
>     which rows? :-)
>
>
> ​The number of rows in the query, as well you know :)

But interestingly

    postgres=# select count(*) where 1=0;
     count
    -------
         0
    (1 row)


--
http://yves.zioup.com
gpg: 4096R/32B0F416

Hi Marc:

On Thu, Jun 11, 2015 at 4:17 PM, Marc Mamin <M.Mamin@intershop.de> wrote:
>>But COUNT(*)
>>does have meaning - it means "the number of rows".
> which rows? :-)

Well, docs could use a little polish there, as the select page says """

Compatibility

Of course, the SELECT statement is compatible with the SQL standard.
But there are some extensions and some missing features.

Omitted FROM Clauses

PostgreSQL allows one to omit the FROM clause. It has a
straightforward use to compute the results of simple expressions:

SELECT 2+2;

 ?column?
----------
        4

Some other SQL databases cannot do this except by introducing a dummy
one-row table from which to do the SELECT.
""""

Old time users have grown used to use it without questioning, and I
think it's sometimes needed to call functions, but some words along
the "a magic one row zero columns table is used when it is omited",
which is what it seems to be done, would be nice.

And, as I said, * only means the columns in a select, I think on no
from Pg may be generating a fake one row table to satisfy the
requirements ( maybe not, but is one easy way to make this work given
how select is explained to work in the docs ).

Francisco Olarte.

On 06/11/2015 07:17 AM, Marc Mamin wrote:
>>> That's the point. * has no meaning without FROM
>
>
>> But COUNT(*)
>>
>> does have meaning - it means "the number of rows".
>
> which rows? :-)

To follow up on the post from Chris Mair:

test=> select count(*), 'foo';
  count | ?column?
-------+----------
      1 | foo

See also:

http://www.postgresql.org/docs/9.4/interactive/functions-aggregate.html


count(*)           bigint     number of input rows
>
>> It's not counting the number of columns in the row, so postgres doesn't need to know what columns exist in the row
toreturn a row count. 
>
>> Geoff
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

> -----Original Message-----
> From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
> Sent: Donnerstag, 11. Juni 2015 16:31
> To: Marc Mamin; 'Geoff Winkless'; Postgres General
> Subject: Re: [GENERAL] select count(*);
> 
> On 06/11/2015 07:17 AM, Marc Mamin wrote:
> >>> That's the point. * has no meaning without FROM
> >
> >
> >> But COUNT(*)
> >>
> >> does have meaning - it means "the number of rows".
> >
> > which rows? :-)
> 
> To follow up on the post from Chris Mair:
> 
> test=> select count(*), 'foo';
>   count | ?column?
> -------+----------
>       1 | foo


Fine, This explains why the result must be 1 :)

Marc

> 
> See also:
> 
> http://www.postgresql.org/docs/9.4/interactive/functions-aggregate.html
> 
> 
> count(*)           bigint     number of input rows
> >
> >> It's not counting the number of columns in the row, so postgres
> doesn't need to know what columns exist in the row to return a row
> count.
> >
> >> Geoff
> >
> >
> 
> 
> --
> Adrian Klaver
> adrian.klaver@aklaver.com