Thread: 2 questions.
Is there a strlen function in sql? Can you do a multiple comparison on the same field. I would like to be able to find entries that have either a null entry or a blank entry as a boolean result and sort by the boolean result. (ie: select field='' or field is null as x from table order by x;) The above statement core dumps. The current solution is to: update table set field='' where field is null; before running the query. There must be a better way? Ideas? Thadvancenks!!!! -- Colin Dick On Call Internet Services cdick@mail.ocis.net
ALERT - cc'd to pgsql-hackers since this looks like a bug in the parser to me. On Tue, 22 Sep 1998, Colin Dick wrote: > Can you do a multiple comparison on the same field. I would like to be > able to find entries that have either a null entry or a blank entry as a > boolean result and sort by the boolean result. In a word Yes - it looks like the parser simply doesn't understand your SQL and craps out on it. > (ie: select field='' or field is null as x from table order by x;) I write this as: select field as x from table where field='' or field is null order by x; which works properly (even tested it on 6.3.2). Either you aren't supposed to write queries as you did or the parser should figure out what you are trying to do. Either way I think it's a bug. You can resolve it yourself by just writing the query as I did. Chris
Hello Colin,
martedì, 22 settembre 98, you wrote:
CD> Is there a strlen function in sql?
The SQL function is:
CHAR_LENGTH
CHARACTER_LENGTH
unfortunately it doesn't work with NULLs you have to exclude them
like this:
SELECT * FROM table
WHERE CHAR_LENGTH(field) = 0 AND field IS NOT NULL OR field IS NULL;
The above command list only the rows where "field" is equal '' or
NULL.
CD> Can you do a multiple comparison on the same field. I would like to be
CD> able to find entries that have either a null entry or a blank entry as a
CD> 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;
This should work, except for ORDER BY because NULL's will be all the
time, at bottom of list even if you specify [DESC]endent order.
I'm sorry but I don't know how to transform NULL's to '', this is also
one of my unsolved problems. We need a function like Oracle NVL() or
ANSI COALESCE() but we don't have this on PostgreSQL.
-----
PS:
For hackers only. IMHO I think we need to do something for NULLs. ;)
What do you think about?
Jose'
> 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.
Thomas G. Lockhart wrote:
>
> > 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...
Yes the problem with NULLS is so big that SQL92 leaves it to
implementation-dependent.
However SQL92 has some functions to help with nulls and I think we NEED
to have
these on PostgreSQL.
For example, I don't know how to compute fields containing nulls,
because the result
is always null.
AFAIK SQL92 has at least two functions that one can use to decide what
to do with nulls. (i.e.:)
o CASE WHEN ... ELSE ... END
and
o COALESCE()
other databases have similar functions, for example Oracle has the
function
NVL() to translate nulls to an arbitrary value.
I tried to write a function to emulate NVL() but it doesn't work because
nulls
have a strange behavior.
int nvl(int arg)
{
if(arg) return arg;
return 0;
}
Any ideas?
>
> - 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.
Don't mention it Tom, it was a very pleasure to me to share a bit of my
time
for this great work and I intend to co-operate again if you need help.
Jose'
> > 1) functions taking or returning "pass by value" arguments
> > 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.
1a) functions taking only "pass by value" arguments are never called
with NULL arguments, since Postgres cannot indicate to the routine that
there is a NULL value (it uses a null pointer to indicate this with
"pass by reference" arguments, such as float8 or text).
> However SQL92 has some functions to help with nulls and I think we
> NEED to have these on PostgreSQL.
> For example, I don't know how to compute fields containing nulls,
> because the result is always null.
>
> AFAIK SQL92 has at least two functions that one can use to decide what
> to do with nulls. (i.e.:)
> o CASE WHEN ... ELSE ... END
> and
> o COALESCE()
> other databases have similar functions, for example Oracle has the
> function NVL() to translate nulls to an arbitrary value.
So Oracle does not have the CASE construct? That seems to be a rich area
for work in v6.5...
> I tried to write a function to emulate NVL() but it doesn't work
> because nulls have a strange behavior.
>
> int nvl(int arg)
> {
> if(arg) return arg;
> return 0;
> }
>
> Any ideas?
You are running into the "pass by value" item (1a) above. And anyway,
you would not be able to do what you want since there is currently no
way to signal back that the input is NULL rather than zero.
In the long run, we would need to either convert all user-accessible
types to be "pass by reference", or would need to implement another
mechanism for signalling NULLness as a return condition (perhaps with a
global variable since each Postgres backend is single-threaded).
This would be a good topic to revisit after v6.4 is released...
- Tom