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@wildenhain.de]
> Sent: Wednesday, November 16, 2005 8:43 AM
> To: Pollard, Mike
> Cc: Richard Huxton; pgsql-hackers@postgresql.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.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Pollard, Mike schrieb:
> 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>).

I'm not getting how you got this idea of count() doing any conversion?
It does not and there is nothing in the docs wich would lead to this.

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

No, count does not convert. It just counts all non null values.
If you want to count rows, just use count(*).

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Yes, sorry for the mess. The problem was somewhere else (not quite
well written log procedure issuing conversion used for logging, even
tho log was off).

--
GJ

"If we knew what we were doing, it wouldn't be called Research, would
it?" - AE




---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org