Cast null to int4 upgrading from Version 7.2

My company is currently using version 7.2 and would like to convert to the
latest version. Unfortunately, version 7.3 implicitly casts a null text to
an int4. For example:

Create table employee_table (

employee_id integer

employee_name text

employee_address text);

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. 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? (I am not interested in debating the
theory of nulls versus zero. I am just trying to avoid unnecessary costs).

I am not a DBA, and am looking for explicit instructions to solve this
problem. Is it possible to create a CAST after upgrading to version 8.2?
My research tells me the following cast was no longer implemented after
version 7.2. Will executing the following CAST solve my problem?

CREATE CAST (text AS int4) WITH FUNCTION int4(text);

If so, can someone give me instructions as to executing this statement?

Any help is appreciated..

Dwight

Dwight Emmons wrote:

[Why did you post this to pgsql-patches of all places? it should
properly have gone to pgsql-general, I think]

> My company is currently using version 7.2 and would like to convert to
> the latest version. Unfortunately, version 7.3 implicitly casts a null
> text to an int4. For example:
>
> Create table employee_table (
>
> employee_id integer
>
> employee_name text
>
> employee_address text);
>
> Select * from employee_table where employee_id = ‘’;
>

That's not a NULL at all, it's an empty string. You really need to
understand the difference between the two.

Old editions of postgres did take an empty string literal as a 0 for
ints, modern version quite rightly reject it as invalid. use NULL if you
mean NULL and 0 if you mean 0.

> When executing this select statement in version 7.2 the null will be
> converted to an int zero and not fail. 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? (I am not interested in
> debating the theory of nulls versus zero. I am just trying to avoid
> unnecessary costs).
>
> I am not a DBA, and am looking for explicit instructions to solve this
> problem. Is it possible to create a CAST after upgrading to version
> 8.2? My research tells me the following cast was no longer implemented
> after version 7.2. Will executing the following CAST solve my problem?
>
> CREATE CAST (text AS int4) WITH FUNCTION int4(text);
>
> If so, can someone give me instructions as to executing this statement?
>
> Any help is appreciated….
>

This has nothing to do with casts, I believe - it has to to with what
the input routines accept.

I strongly suspect that renovating your code is your best choice, much
as that might pain you.

cheers

andrew

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

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)

Jim Nasby wrote:
> 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.

Won't work. You need to replace the data type input function.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

you might be interested using the patch attached, it introduces a new GUC
variable named "empty_equals_0", allowing to set this per database:
alter database xyz set empty_equals_0 to true;

I've written and used this patch for internal purpose, and take no
responsibilty at all, but it's working (having 60000 lines old application
code using this)

Am Donnerstag, 16. November 2006 22:47 schrieb Dwight Emmons:
> My company is currently using version 7.2 and would like to convert to the
> latest version. Unfortunately, version 7.3 implicitly casts a null text
> to an int4. For example:
>
>
>
> Create table employee_table (
>
> employee_id integer
>
> employee_name text
>
> employee_address text);
>
>
>
> 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. 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? (I am not interested in debating the
> theory of nulls versus zero. I am just trying to avoid unnecessary costs).
>
>
>
> I am not a DBA, and am looking for explicit instructions to solve this
> problem. Is it possible to create a CAST after upgrading to version 8.2?
> My research tells me the following cast was no longer implemented after
> version 7.2. Will executing the following CAST solve my problem?
>
>
>
> CREATE CAST (text AS int4) WITH FUNCTION int4(text);
>
>
>
> If so, can someone give me instructions as to executing this statement?
>
>
>
> Any help is appreciated..
>
>
>
> Dwight