Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NULL safe equality operator
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; ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] NULL safe equality operator
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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] NULL safe equality operator
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] NULL safe equality operator
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NULL safe equality operator
Christopher Kings-Lynne [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] NULL safe equality operator
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
