pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do you compare (NULL) and (non-NULL)?
6+ messages / 4 participants
[nested] [flat]
* How do you compare (NULL) and (non-NULL)?
@ 2004-10-26 20:23 Wei Weng <wweng@kencast.com>
0 siblings, 2 replies; 6+ messages in thread
From: Wei Weng @ 2004-10-26 20:23 UTC (permalink / raw)
To: pgsql-sql
In the following query
SELECT Parent FROM Channels ORDER BY Parent ASC;
If I have a couple of (NULL)s in the field [Parent], they will be listed at
the bottom of the query result.
Is it because PostgreSQL considers (NULL) as the biggest value? If I run the
same query under MSSQL Server 2000, I get the exact opposite result
regarding the order of (NULL)s and (non-NULL) values. They are listed at the
very beginning of the query result.
Thanks
Wei
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do you compare (NULL) and (non-NULL)?
@ 2004-10-26 20:48 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Wei Weng <wweng@kencast.com>
1 sibling, 1 reply; 6+ messages in thread
From: Stephan Szabo @ 2004-10-26 20:48 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
On Tue, 26 Oct 2004, Wei Weng wrote:
> In the following query
>
> SELECT Parent FROM Channels ORDER BY Parent ASC;
>
> If I have a couple of (NULL)s in the field [Parent], they will be listed at
> the bottom of the query result.
>
> Is it because PostgreSQL considers (NULL) as the biggest value? If I run the
> same query under MSSQL Server 2000, I get the exact opposite result
> regarding the order of (NULL)s and (non-NULL) values. They are listed at the
> very beginning of the query result.
The spec basically says (IIRC) that implementations must either treat all
nulls as greater than all non-nulls for ordering or less than all
non-nulls for ordering, but that different implementations may choose
different choices. I think the most recent version (at least) provides an
option to specify which way to handle nulls, but we don't support that as
far as I know.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do you compare (NULL) and (non-NULL)?
@ 2004-10-26 21:01 Jerome Alet <alet@librelogiciel.com>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jerome Alet @ 2004-10-26 21:01 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: pgsql-sql
On Tue, Oct 26, 2004 at 01:48:48PM -0700, Stephan Szabo wrote:
> On Tue, 26 Oct 2004, Wei Weng wrote:
>
> > In the following query
> >
> > SELECT Parent FROM Channels ORDER BY Parent ASC;
> >
> > If I have a couple of (NULL)s in the field [Parent], they will be listed at
> > the bottom of the query result.
> >
> > Is it because PostgreSQL considers (NULL) as the biggest value? If I run the
> > same query under MSSQL Server 2000, I get the exact opposite result
> > regarding the order of (NULL)s and (non-NULL) values. They are listed at the
> > very beginning of the query result.
you could try to use COALESCE to treat NULLs as either a minimal or
maximal value so that your ordering is correct :
SELECT Parent FROM Channels ORDER BY COALESCE(Parent, -1) ASC;
to treat NULLs as -1 for example
hth
Jerome Alet
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do you compare (NULL) and (non-NULL)?
@ 2004-10-27 15:36 Bruno Wolff III <bruno@wolff.to>
parent: Wei Weng <wweng@kencast.com>
1 sibling, 1 reply; 6+ messages in thread
From: Bruno Wolff III @ 2004-10-27 15:36 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
On Tue, Oct 26, 2004 at 16:23:20 -0400,
Wei Weng <wweng@kencast.com> wrote:
> In the following query
>
> SELECT Parent FROM Channels ORDER BY Parent ASC;
>
> If I have a couple of (NULL)s in the field [Parent], they will be listed at
> the bottom of the query result.
>
> Is it because PostgreSQL considers (NULL) as the biggest value? If I run
> the same query under MSSQL Server 2000, I get the exact opposite result
> regarding the order of (NULL)s and (non-NULL) values. They are listed at
> the very beginning of the query result.
If the order matters, you can order by IS NULL or IS NOT NULL.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do you compare (NULL) and (non-NULL)?
@ 2004-10-29 15:59 Wei Weng <wweng@kencast.com>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 6+ messages in thread
From: Wei Weng @ 2004-10-29 15:59 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
Bruno Wolff III wrote:
> On Tue, Oct 26, 2004 at 16:23:20 -0400,
> Wei Weng <wweng@kencast.com> wrote:
>
>>In the following query
>>
>>SELECT Parent FROM Channels ORDER BY Parent ASC;
>>
>>If I have a couple of (NULL)s in the field [Parent], they will be listed at
>>the bottom of the query result.
>>
>>Is it because PostgreSQL considers (NULL) as the biggest value? If I run
>>the same query under MSSQL Server 2000, I get the exact opposite result
>>regarding the order of (NULL)s and (non-NULL) values. They are listed at
>>the very beginning of the query result.
>
>
> If the order matters, you can order by IS NULL or IS NOT NULL.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>
How do I write that?
Thanks
Wei
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How do you compare (NULL) and (non-NULL)?
@ 2004-10-29 17:57 Bruno Wolff III <bruno@wolff.to>
parent: Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2004-10-29 17:57 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
On Fri, Oct 29, 2004 at 11:59:15 -0400,
Wei Weng <wweng@kencast.com> wrote:
>
> How do I write that?
SELECT Parent FROM Channels ORDER BY Parent IS NULL, Parent ASC;
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-10-29 17:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-26 20:23 How do you compare (NULL) and (non-NULL)? Wei Weng <wweng@kencast.com>
2004-10-26 20:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-10-26 21:01 ` Jerome Alet <alet@librelogiciel.com>
2004-10-27 15:36 ` Bruno Wolff III <bruno@wolff.to>
2004-10-29 15:59 ` Wei Weng <wweng@kencast.com>
2004-10-29 17:57 ` Bruno Wolff III <bruno@wolff.to>
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