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
diff -Nurb postgresql-8.1.4/src/backend/utils/adt/numutils.c postgresql-8.1.4-patched/src/backend/utils/adt/numutils.c
--- postgresql-8.1.4/src/backend/utils/adt/numutils.c 2005-12-01 00:10:16.000000000 +0100
+++ postgresql-8.1.4-patched/src/backend/utils/adt/numutils.c 2006-10-13 12:05:34.000000000 +0200
@@ -55,6 +55,9 @@
* Unlike plain atoi(), this will throw ereport() upon bad input format or
* overflow.
*/
+
+bool empty_equals_0;
+
int32
pg_atoi(char *s, int size, int c)
{
@@ -68,6 +71,9 @@
if (s == NULL)
elog(ERROR, "NULL pointer");
if (*s == 0)
+ if(empty_equals_0)
+ return (int32)0;
+ else
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for integer: \"%s\"",
diff -Nurb postgresql-8.1.4/src/backend/utils/misc/guc.c postgresql-8.1.4-patched/src/backend/utils/misc/guc.c
--- postgresql-8.1.4/src/backend/utils/misc/guc.c 2006-05-21 22:11:02.000000000 +0200
+++ postgresql-8.1.4-patched/src/backend/utils/misc/guc.c 2006-10-13 12:05:57.000000000 +0200
@@ -97,6 +97,7 @@
extern int CommitSiblings;
extern char *default_tablespace;
extern bool fullPageWrites;
+extern bool empty_equals_0;
#ifdef TRACE_SORT
extern bool trace_sort;
@@ -395,6 +396,14 @@
static struct config_bool ConfigureNamesBool[] =
{
{
+ {"empty_equals_0", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
+ gettext_noop("Treats the empty string as 0 when dealing with integers"),
+ NULL
+ },
+ &empty_equals_0,
+ false, NULL, NULL
+ },
+ {
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
NULL
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
