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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
