pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT MAX returns wrong value
3+ messages / 3 participants
[nested] [flat]
* SELECT MAX returns wrong value
@ 2007-12-13 23:09 Gavin Baumanis <gavinb@eclinic.com.au>
0 siblings, 2 replies; 3+ messages in thread
From: Gavin Baumanis @ 2007-12-13 23:09 UTC (permalink / raw)
To: pgsql-sql
Hi Everyone,
Sorry if I am missing something obvious but I think I have found a bug.
If I perform the following SQL
SELECT MAX(column) FROM table WHERE expression
and there is no match, Postgres returns a record count of 1.
There is no value in max, it is NULL.
Thus trying to perform some "other" tasks based on if a record was
returned or not is proving a little difficult in this instance.
We are using 8.1.9 on Debian.
Thanks for any help you might be able to offer.
--
If I can ever be of any further assistance, please contact me.
Gavin 'Beau' Baumanis
eClinic Pty Ltd
ABN 80 092 450 274
657 Nicholson Street
Carlton North
Victoria 3054
Australia
P: +61 3 9381 4567
F: +61 3 9381 4657
M: +61 438 545 586
E: beau@eclinic.com.au
W: http://www.eclinic.com.au
Attachments:
gavinb.vcf (text/x-vcard, 503B) download
begin:vcard
fn:Beau
n:Baumanis;Gavin
org:PalCare Pty. Ltd.
adr:;;657 Nicholson Street;Carlton North;Melbourne;3054;Australia
email;internet:beau@eclinic.com.au
title:Application Developer
tel;work:+61-3 9381-4567
tel;cell:+61-438-545-586
note;quoted-printable:Certified Novell Engineer (CNE)=0D=0A=
Microsoft Certificed Systems Engineer (MCSE)=0D=0A=
Post Graduate Certificate in IT=0D=0A=
Certificate III Electronics Technician
x-mozilla-html:TRUE
url:http://www.palcare.com.au
version:2.1
end:vcard
* Re: SELECT MAX returns wrong value
@ 2007-12-13 23:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gavin Baumanis <gavinb@eclinic.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2007-12-13 23:32 UTC (permalink / raw)
To: Gavin Baumanis <gavinb@eclinic.com.au>; +Cc: pgsql-sql
Gavin Baumanis <gavinb@eclinic.com.au> writes:
> If I perform the following SQL
> SELECT MAX(column) FROM table WHERE expression
> and there is no match, Postgres returns a record count of 1.
> There is no value in max, it is NULL.
This is the behavior required by the SQL standard.
> Thus trying to perform some "other" tasks based on if a record was
> returned or not is proving a little difficult in this instance.
Test for null result, perhaps?
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SELECT MAX returns wrong value
@ 2007-12-14 17:24 Scott Marlowe <scott.marlowe@gmail.com>
parent: Gavin Baumanis <gavinb@eclinic.com.au>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Marlowe @ 2007-12-14 17:24 UTC (permalink / raw)
To: Gavin Baumanis <gavinb@eclinic.com.au>; +Cc: pgsql-sql
On Dec 13, 2007 5:09 PM, Gavin Baumanis <gavinb@eclinic.com.au> wrote:
> Hi Everyone,
>
> Sorry if I am missing something obvious but I think I have found a bug.
> If I perform the following SQL
>
> SELECT MAX(column) FROM table WHERE expression
>
> and there is no match, Postgres returns a record count of 1.
> There is no value in max, it is NULL.
that's because you got one record back. A null one, but a record none the less.
The standard way of doing this is:
select count(column) from table where expression.
since null columns don't get counted, it will return zero if they're all null.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-14 17:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-13 23:09 SELECT MAX returns wrong value Gavin Baumanis <gavinb@eclinic.com.au>
2007-12-13 23:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-14 17:24 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox