Обсуждение: Postgres 8 - problem: invalid input syntax for integer

Hi, In previous version di Postgres (7.2) I used this table:
CREATE TABLE tablename (id serial, field int1, field2 text);

Now this query work:
UPDATE tablename SET field1=''; (NOTE: implicit conversion to 0)
UPDATE tablename SET field2='';

(this cause of simple code-generation query - I don't know what's
field type)

Now in postgres 8 this don't work. 
Why ?(ok, it's the ufficial documentation but I don't understand...
why? it's so comfortable!)
Can someone help me to create a CAST to re-use this feature?
Thank you! Mauro

mauro wrote:
> Hi, In previous version di Postgres (7.2) I used this table:
> CREATE TABLE tablename (id serial, field int1, field2 text);
> 
> Now this query work:
> UPDATE tablename SET field1=''; (NOTE: implicit conversion to 0)
> UPDATE tablename SET field2='';
> 
> (this cause of simple code-generation query - I don't know what's
> field type)

Know your field-types. If you don't know what they are, you can't handle 
errors elegantly anyway.

> Now in postgres 8 this don't work. 
> Why ?(ok, it's the ufficial documentation but I don't understand...
> why? it's so comfortable!)

What number does '' represent?
Does that mean a string of '/2' should equal your number divided by two? 
If not, why not?
Who is providing an empty string where you've asked for a number, and 
why not trap this error (or store a NULL)?

> Can someone help me to create a CAST to re-use this feature?

Well, you could create a function:
CREATE FUNCTION empty_string_is_zero(text) RETURNS integer AS '  SELECT CASE    WHEN $1='''' THEN 0    ELSE $1::integer
END;
 
' LANGUAGE SQL;

UPDATE my_table SET myfield=empty_string_is_zero('');

HTH
--  Richard Huxton  Archonet Ltd

> What number does '' represent?
'No response' value...
> Does that mean a string of '/2' should equal your number divided by two? 
right, but it is never required.
> If not, why not?
because I use it to GROUP BY values.
> 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!

[...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).

Best regards, Mauro

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
functionsthat 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