Thread: NULL safe equality operator
Hi guys, Does anyone know how I'd go about implementing the following MySQL operator in PostgreSQL? --- NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand isNULL. mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL; -> 1, 1, 0 mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL; -> 1, NULL, NULL --- Chris
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
> 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: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
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 asnull_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