Message-ID: <4222E59B.3050205@archonet.com>
Date: Mon, 28 Feb 2005 09:34:19 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: mauro <bertolima@yahoo.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: Postgres 8 - problem: invalid input syntax for integer
References: <d64dda1e.0502230219.21e243b1@posting.google.com>
	<421D8CEF.7050900@archonet.com>
	<d64dda1e.0502240704.11d165ca@posting.google.com>
In-Reply-To: <d64dda1e.0502240704.11d165ca@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

mauro wrote:
>>What number does '' represent?
> 
> 'No response' value...

Would've been better to have a genuine response_provided flag, but then 
you obviously know that.

>>Who is providing an empty string where you've asked for a number, and 
>>why not trap this error (or store a NULL)?
> 
> You are certainly right. My problem concerns the compatibility of code
> among postgres 8 and 7.2 that I wanted to maintain. The existing code
> (data analysis) exploits the particularity that the null ('') becomes
> 0 (ok, no comment :) ) logically wrong but practically perfect!

Your best choice is probably to tweak your application and translate '' 
to NULL? Then you could add a before trigger to the table to replace 
NULL with 0.

> [...CAST CODE...]
> 
> Thank you for the explicit-cast code, but I want reproduce it in
> 'database level' so I don't use explicit cast but IMPLICIT; everytime
> updating integer fields with '' values it cast to (0 or NULL).

If you can't do the above, you've got three options:
  1. Stay with PG version 7.2
  2. Write your own type, with in/out functions that map '' to 0
  3. Hack the code to replace the ''=>0 conversion - you could probably 
identify the old code from CVS.

Obviously the null+trigger option is better than these three. Of these 
three though, number 2 is probably the cleanest solution.
--
   Richard Huxton
   Archonet Ltd