pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thomas G. Lockhart <lockhart@alumni.caltech.edu>
To: Sferacarta Software <sferac@bo.nettuno.it>
To: Colin Dick <cdick@mail.ocis.net>
Cc: pgsql-sql@postgreSQL.org
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] 2 questions.
Date: Thu, 24 Sep 1998 02:34:24 +0000
Message-ID: <3609AFB0.A525E25D@alumni.caltech.edu> (raw)
References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net>
	<14656.980923@bo.nettuno.it>

> CD> I would like to...
> CD> ... find entries that have either a null entry or a blank entry as 
> CD> a boolean result and sort by the boolean result.
> CD> (ie: select field='' or field is null as x from table order by x;)
> SELECT field
>        FROM table
>        WHERE field='' OR field IS NULL
>        ORDER BY field;

The problem statement isn't really clear, but Colin's prototype query
suggests that he wants all fields back, with a boolean column "true" if
the field is zero-length or if the field is NULL.

So I think his original proposal is the one he wants:

  SELECT (field = '' OR field IS NULL)
    FROM table
    ORDER by 1;

> For hackers only. IMHO I think we need to do something for NULLs. ;)
> What do you think about?

The only open issues on NULLs afaik (I'm doing this from memory, so if
there are other things on the ToDo don't take this as having removed
them :) are:

1) functions taking or returning "pass by value" arguments, as is
typical for functions returning int4 (and other 4-byte or shorter data
types except for float4), cannot signal that the returned value should
actually be NULL. So they must throw an error instead. That's why
char_length() doesn't behave gracefully with nulls.

2) NULL fields don't move from front-to-back or back-to-front when
changing the sort order from ascending to descending. I don't speak for
the Postgres team on this, but frankly I don't see this as a big issue.
I know that SQL92 specifies that nulls *should* switch ends of the
returned list, but the standard also says that *which* end of the list
they are on for, say, ascending order, is implementation-dependent. So,
it seems like portable code really needs to handle both cases anyway...
imho it is a place where the standard probably should have said less
(or, depending on your preference, more) and is inadequate as-is. If you
are getting back a mix of nulls and values, check each returned row for
whether the value is null and you won't get in trouble...

                     - Tom

btw, Thanks again Jose' for those great SQL reference pages. Oliver and
I have got them transcribed to sgml, and they will make a nice addition
to the v6.4 release of the docs.



view thread (6+ messages)  latest in thread

Message-ID: <3609AFB0.A525E25D@alumni.caltech.edu>
Permalink:  ../3609AFB0.A525E25D@alumni.caltech.edu/
Also on:    postgresql.org/message-id/3609AFB0.A525E25D@alumni.caltech.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: lockhart@alumni.caltech.edu, sferac@bo.nettuno.it, cdick@mail.ocis.net, pgsql-hackers@postgreSQL.org
  Subject: Re: [HACKERS] Re: [SQL] 2 questions.
  In-Reply-To: <3609AFB0.A525E25D@alumni.caltech.edu>
  References: <Pine.LNX.3.96.980922095650.17825A-100000@ocis.ocis.net> <14656.980923@bo.nettuno.it> <3609AFB0.A525E25D@alumni.caltech.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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