Message-ID: <421D8CEF.7050900@archonet.com>
Date: Thu, 24 Feb 2005 08:14:39 +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>
In-Reply-To: <d64dda1e.0502230219.21e243b1@posting.google.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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