Re: Cast null to int4 upgrading from Version 7.2

FYI, the pgsql-patches list is for proposed patches, not questions about
behavior.

On Thu, 2006-11-16 at 13:47 -0800, Dwight Emmons wrote:
> Select * from employee_table where employee_id = ‘’;

> When executing this select statement in version 7.2 the null will be
> converted to an int zero and not fail.

Your statement does not include a "null" value, it includes an empty
string. The behavior you're depending on is that an empty string was
treated as zero in input to an integer type, which is no longer the
case. It has nothing to do with casting AFAICS.

>  In version 8.2 it fails.  We have over 20,000 lines of code and do
> not want to modify and test all of it.  Has anyone come across this
> problem?

Yes, this is a common problem for people upgrading from 7.2. I think the
long-term fix is to change your queries: comparing an integer with '' is
not sensible. That is:

SELECT * FROM employee_table WHERE employee_id = 0;

is the right way to write that query.

As a temporary fix, I suppose you could hack pg_atoi() to treat an empty
string as zero (src/backend/utils/adt/numutils.c).

-Neil