NULL safe equality operator

On Nov 25, 2005, at 11:17 , Christopher Kings-Lynne wrote:

Hi guys,

Does anyone know how I'd go about implementing the following MySQL
operator in PostgreSQL?

I'm sure you know how to implement this with a stored procedure.
AFAICT, if you wanted to actually implement this as an operator,
you'd need to write C procedures for each datatype to make it an
operator. Is that something you're looking at doing?

Michael Glaesemann
grzm myrealbox com

create or replace function null_safe_cmp (integer, integer) returns int
immutable language sql as $$
select case
when $1 is null and $2 is null then 1
when ($1 is null and $2 is not null)
or ($1 is not null and $2 is null) then 0
else case when $1 = $2 then 1 else 0 end
end;
$$;

test=# select null_safe_cmp(1,1);
null_safe_cmp
---------------
1
(1 row)

test=# select null_safe_cmp(0,1);
null_safe_cmp
---------------
0
(1 row)

test=# select null_safe_cmp(1,0);
null_safe_cmp
---------------
0
(1 row)

test=# select null_safe_cmp(NULL,1);
null_safe_cmp
---------------
0
(1 row)

test=# select null_safe_cmp(1,NULL);
null_safe_cmp
---------------
0
(1 row)

test=# select null_safe_cmp(NULL,NULL);
null_safe_cmp
---------------
1
(1 row)

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

Does anyone know how I'd go about implementing the following MySQL
operator in PostgreSQL?

NOT (x IS DISTINCT FROM y) would be the standard-compliant way of
spelling that.

regards, tom lane

On Thu, 24 Nov 2005, Tom Lane wrote:

NOT (x IS DISTINCT FROM y) would be the standard-compliant way of
spelling that.

That's the sql99 way. In sql2003 (but not in pg) one can also do

X IS NOT DISTINCT FROM y

--
/Dennis Bj�rklund

when ($1 is null and $2 is not null)
or ($1 is not null and $2 is null) then 0

That's the same as:

when $1 is null != $2 is null then 0

Chris

On Nov 25, 2005, at 16:24 , Christopher Kings-Lynne wrote:

when ($1 is null and $2 is not null)
or ($1 is not null and $2 is null) then 0

That's the same as:

when $1 is null != $2 is null then 0

Yeah, I saw your commit. Nice shortcut. Also didn't know you could
define operators using SQL functions. Tom's suggestion of NOT (a
DISTINCT FROM b) is really cool. Much cleaner in my opinion. I learn
a lot from these lists :)

Michael Glaesemann
grzm myrealbox com

On Nov 25, 2005, at 16:37 , Michael Glaesemann wrote:

Tom's suggestion of NOT (a DISTINCT FROM b) is really cool.

Even cooler if I spell it correctly: NOT (a IS DISTINCT FROM b)

Michael Glaesemann
grzm myrealbox com

Yeah, I saw your commit. Nice shortcut. Also didn't know you could
define operators using SQL functions. Tom's suggestion of NOT (a
DISTINCT FROM b) is really cool. Much cleaner in my opinion. I learn a
lot from these lists :)

Needs to return 0 or 1 though.

Chris

On Nov 25, 2005, at 16:43 , Christopher Kings-Lynne wrote:

Yeah, I saw your commit. Nice shortcut. Also didn't know you
could define operators using SQL functions. Tom's suggestion of
NOT (a DISTINCT FROM b) is really cool. Much cleaner in my
opinion. I learn a lot from these lists :)

Needs to return 0 or 1 though.

CREATE OR REPLACE FUNCTION null_safe_cmp (ANYELEMENT, ANYELEMENT)
RETURNS INTEGER IMMUTABLE
LANGUAGE SQL AS $$
SELECT CASE
WHEN NOT ($1 IS DISTINCT FROM $2) THEN 1
ELSE 0
END;
$$;

select null_safe_cmp (1,1) as "(1,1)"
, null_safe_cmp (1,0) as "(1,0)"
, null_safe_cmp (1,NULL) as "(1,NULL)"
, null_safe_cmp (NULL,1) as "(NULL,1)"
, null_safe_cmp (NULL::integer,NULL::integer) as "(NULL,NULL)";
(1,1) | (1,0) | (1,NULL) | (NULL,1) | (NULL,NULL)
-------+-------+----------+----------+-------------
1 | 0 | 0 | 0 | 1
(1 row)

test=# select null_safe_cmp (NULL,NULL);
ERROR: could not determine anyarray/anyelement type because input
has type "unknown"
test=# select null_safe_cmp (NULL::integer,NULL::integer);
null_safe_cmp
---------------
1
(1 row)

Same casting problem due to anyelement, of course.

Michael Glaesemann
grzm myrealbox com

test=# select null_safe_cmp (NULL,NULL);
ERROR: could not determine anyarray/anyelement type because input has
type "unknown"
test=# select null_safe_cmp (NULL::integer,NULL::integer);
null_safe_cmp
---------------
1
(1 row)

Same casting problem due to anyelement, of course.

Yes - I wonder what the trick to getting around that is?

Chris

CREATE OR REPLACE FUNCTION null_safe_cmp (ANYELEMENT, ANYELEMENT)
RETURNS INTEGER IMMUTABLE
LANGUAGE SQL AS $$
SELECT CASE
WHEN NOT ($1 IS DISTINCT FROM $2) THEN 1
ELSE 0
END;
$$;

Even cooler:

CREATE OR REPLACE FUNCTION null_safe_cmp(anyelement, anyelement)
RETURNS integer AS '
SELECT (NOT ($1 IS DISTINCT FROM $2))::integer
' IMMUTABLE LANGUAGE SQL;

On Nov 25, 2005, at 17:06 , Christopher Kings-Lynne wrote:

test=# select null_safe_cmp (NULL,NULL);
ERROR: could not determine anyarray/anyelement type because
input has type "unknown"
test=# select null_safe_cmp (NULL::integer,NULL::integer);
null_safe_cmp
---------------
1
(1 row)
Same casting problem due to anyelement, of course.

Yes - I wonder what the trick to getting around that is?

Don't use NULL.

Michael Glaesemann
grzm myrealbox com

Christopher Kings-Lynne wrote:

Needs to return 0 or 1 though.

All Boolean operators in MySQL do that, so to create an illusion of
MySQL compatibility, you'd need to redefine all standard Boolean
operators. I don't think you want that.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Hello there,

I have included the ipv6 auth. line in my pg_hba.conf file(::1/128)
I keep getting error msgs from postmaster everytime I try to
connect.

Going by previous posts on the topic am unable to conclude.
Does this mean pg 8.0.3 doesn't support ipv6 client auth. ??
Or is there a patch somewhere ??
Plz reply.Thanks in advance.

--
Rajesh R

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

test=# select null_safe_cmp (NULL,NULL);
ERROR: could not determine anyarray/anyelement type because input has
type "unknown"
Same casting problem due to anyelement, of course.

Yes - I wonder what the trick to getting around that is?

You might be able to hack it by creating a second function defined as
null_safe_cmp(unknown,unknown)
Pretty grotty of course, and I'm not sure that it comes up in the
real world as opposed to test cases.

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

Christopher Kings-Lynne wrote:

Needs to return 0 or 1 though.

All Boolean operators in MySQL do that, so to create an illusion of
MySQL compatibility, you'd need to redefine all standard Boolean
operators. I don't think you want that.

Wonder if making an implicit cast from bool to int would answer for
that.

regards, tom lane