Thread: 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". 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
>>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
>But COUNT(*)
>
>does have meaning - it means "the number of rows".
which rows? :-)
The number of rows in the query, as well you know :)
The reason you can't SELECT *; is because there's no way of defining what "*" refers to in this instance. You don't need to define what (*) is in order to tell that there's exactly one row in it.
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
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)
As expected.
=#
SELECT
1 WHERE 1=0; ?column?
----------
(0 rows)
Geoff