From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Baldur Norddahl <bbn-pgsql(dot)general(at)clansoft(dot)dk> |
Cc: | Guy Fraser <guy(at)incentre(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: why the need for is null? |
Date: | 2004-01-03 23:07:48 |
Message-ID: | 3FF74B44.7070804@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Baldur Norddahl wrote:
>Will postgresql still make effective use of the indexes if I use a function like
>coalesce on the column before comparing it?
>
>
PostgreSQL doesn't index NULLs, which may or may not be a problem for
you. Perhaps creating a functional index on the COALESCE(myfield, '')
would achieve what you want, if you are querying the data in a similar
manner.
>1) I understand the issues involved perfectly. I just happens to have a table
>where it would be usefull that NULL=NULL is true. It is not so, and therefore I
>have to use a syntax that is hard to read and I have been made to understand
>that I will have to accept that. Fine.
>
>
If you don't want to change your code, you can optionally set
TRANSFORM_NULL_EQUALS to TRUE in postgresql.conf:
http://www.postgresql.org/docs/current/static/runtime-config.html
This is a parse-time transformation, so a comparison between two
attributes whose value is NULL using the equality operator will still
yield NULL:
[test(at)lexus] select NULL = NULL;
?column?
----------
t
(1 row)
[test(at)lexus] create table foo (x integer, y integer);
CREATE TABLE
[test(at)lexus] insert into foo values (NULL, NULL);
INSERT 164948 1
[test(at)lexus] select (x = y) from foo;
?column?
----------
(1 row)
[test(at)lexus] select (x = NULL) from foo;
?column?
----------
t
(1 row)
I suggest it only as a temporary stop-gap until the code can be changed
into something SQL compliant.
>2) What kind of crap is that flaming me like this? Do all users that ask a
>question about why postgresql or the sql standard implements a feature in a
>specific way, end up being told to switch to mysql?
>
>
No. :-)
Mike Mascari
From | Date | Subject | |
---|---|---|---|
Next Message | Joe Conway | 2004-01-03 23:10:11 | Re: Passing a comma delimited list to a function |
Previous Message | Marc G. Fournier | 2004-01-03 23:06:24 | Re: website doc search is extremely SLOW |