Re: why the need for is null?

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@lexus] select NULL = NULL;
 ?column?
----------
 t
(1 row)

[test@lexus] create table foo (x integer, y integer);
CREATE TABLE
[test@lexus] insert into foo values (NULL, NULL);
INSERT 164948 1
[test@lexus] select (x = y) from foo;
 ?column?
----------

(1 row)

[test@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