Re: Cast null to int4 upgrading from Version 7.2

On Nov 16, 2006, at 3:10 PM, Neil Conway wrote:
> 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).

As a less invasive alternative, I *think* you could create an SQL
function for casting text to int that treated '' as 0, and then
replace the built-in CAST with that. You'd also need to make the cast
implicit, which could cause other problems.

20k lines of code isn't all that much, though... you'll be much
better off fixing it.
--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)