How do you compare (NULL) and (non-NULL)?

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: How do you compare (NULL) and (non-NULL)?

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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: How do you compare (NULL) and (non-NULL)?

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: How do you compare (NULL) and (non-NULL)?

On Tue, Oct 26, 2004 at 16:23:20 -0400,
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.

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

Re: How do you compare (NULL) and (non-NULL)?

Bruno Wolff III wrote:
> On Tue, Oct 26, 2004 at 16:23:20 -0400,
> 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.
>
>
> 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






---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: How do you compare (NULL) and (non-NULL)?

On Fri, Oct 29, 2004 at 11:59:15 -0400,
Wei Weng wrote:
>
> How do I write that?

SELECT Parent FROM Channels ORDER BY Parent IS NULL, Parent ASC;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster