Re: Differences in WHERE clause of SELECT

Hi,

Thanks for your responses.

The specific use case which I am interested in is

" Numeric LIKE Pattern_string ".

I'm willing to attempt a patch to support the specific use case above by adding implicit casts, without modifying the entire casting rules.

Is this something that is likely to be included in the code ?

Thanks & Regards,
Vaishnavi

-----Original Message-----
From: pgsql-hackers-owner(at)postgresql(dot)org [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Kevin Grittner
Sent: Wednesday, 17 July 2013 6:23 AM
To: Robert Haas; Merlin Moncure
Cc: Tom Lane; Josh Berkus; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [HACKERS] Differences in WHERE clause of SELECT

Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> We can certainly continue to play whack-a-mole and dream up a new
> solution every time a really intolerable variant of this problem comes
> up.  But that doesn't seem good to me.  It means that every case
> behaves a little different from every other case, and the whole thing
> is kinda arcane and hard to understand, even for hackers.

If you're building up a list of things that generate errors in PostgreSQL but not other DBMS products, make sure you have this:

test=# create table t(d date);
CREATE TABLE
test=# insert into t values (NULL);
INSERT 0 1
test=# insert into t values (COALESCE(NULL, NULL));
ERROR:  column "d" is of type date but expression is of type text LINE 1: insert into t values (COALESCE(NULL, NULL));
                              ^
HINT:  You will need to rewrite or cast the expression.

From a user perspective, it's hard to explain why COALESCE(NULL,
NULL) fails in a location that a bare NULL works.  From the perspective of those working on the code, and looking at the problem from the inside out, it seems sane; but that's the only perspective from which it does.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers