Re: question about count(b) where b is a custom type

If count(<col>) convert <col> to a string (an assumption that Martijn
has cast into doubt, or perhaps shredded), then rather than convert all
non-nulls that are not a string into a string, I was proposing
converting the values into an int with the values 0 or 1 (0 means that
row was null for that column, 1 means that row was not null;, since
count(<col>) means count the non-null rows in <col>).

Anyway, to make a short story long. The idea is rather than convert the
column into a string, convert it into a value indicating whether the
column was null or not null (which is all count cares about). In any
case, it's moot idea since it appears Postgres already does that.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------
Better to remain silent and be thought a fool than to speak out and
remove all doubt.
Abraham Lincoln

> -----Original Message-----
> From: Tino Wildenhain [mailto:tino(at)wildenhain(dot)de]
> Sent: Wednesday, November 16, 2005 8:43 AM
> To: Pollard, Mike
> Cc: Richard Huxton; pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] question about count(b) where b is a custom
type
>
> Pollard, Mike schrieb:
> > Richard Huxton wrote:
> >
> >>Pollard, Mike wrote:
> >>
> >>>>Firstly, if you just want a count, what's wrong with count(1) or
> >>>>count(*).
> >>>>
> >>>
> >>>
> >>>Because unless the column does not allow nulls, they will not
return
> >
> > the
> >
> >>>same value.
> >>
> >>Ah, but in the example given the column was being matched against a
> >>value, so nulls were already excluded.
> >>
> >>--
> >
> >
> > Details, details. But there is a valid general question here, and
> > changing the semantics of the query will not address it. When doing
a
> > count(col), why convert col into a string just so you can determine
if
> > it is null or not? This isn't a problem on a small amount of data,
but
>
> Why convert? A null is always null no matter in which datatype.
>
> > it seems like a waste, especially if you are counting millions of
> > records. Is there some way to convert this to have the caller
convert
> > nulls to zero and non-nulls to 1, and then just pass an int? So
> > logically the backend does:
> >
> > Select count(case <col> when null then 0 else 1) from <table>
>
> Which would be totally silly :-) no matter if its 0 or 1
> it counts as 1. Do you mean sum() maybe?
> Even then you dont need coalesce to convert null to 0
> because sum() just ignores null.
>
>
> > And count just adds the number to the running tally.
>
> Which number here?
>
> >
> > Mike Pollard
> > SUPRA Server SQL Engineering and Support
> strange...
>
> > Cincom Systems, Inc.