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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
