pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Postgres 8 - problem: invalid input syntax for integer 4+ messages / 2 participants [nested] [flat]
* Postgres 8 - problem: invalid input syntax for integer @ 2005-02-23 10:19 mauro <bertolima@yahoo.it> 2005-02-24 08:14 ` Re: Postgres 8 - problem: invalid input syntax for integer Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 4+ messages in thread From: bertolima@yahoo.it @ 2005-02-23 10:19 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 8 - problem: invalid input syntax for integer 2005-02-23 10:19 Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> @ 2005-02-24 08:14 ` Richard Huxton <dev@archonet.com> 2005-02-24 15:04 ` Re: Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> 0 siblings, 1 reply; 4+ messages in thread From: Richard Huxton @ 2005-02-24 08:14 UTC (permalink / raw) To: mauro <bertolima@yahoo.it>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 8 - problem: invalid input syntax for integer 2005-02-23 10:19 Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> 2005-02-24 08:14 ` Re: Postgres 8 - problem: invalid input syntax for integer Richard Huxton <dev@archonet.com> @ 2005-02-24 15:04 ` mauro <bertolima@yahoo.it> 2005-02-28 09:34 ` Re: Postgres 8 - problem: invalid input syntax for integer Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 4+ messages in thread From: bertolima@yahoo.it @ 2005-02-24 15:04 UTC (permalink / raw) To: pgsql-sql > 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres 8 - problem: invalid input syntax for integer 2005-02-23 10:19 Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> 2005-02-24 08:14 ` Re: Postgres 8 - problem: invalid input syntax for integer Richard Huxton <dev@archonet.com> 2005-02-24 15:04 ` Re: Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> @ 2005-02-28 09:34 ` Richard Huxton <dev@archonet.com> 0 siblings, 0 replies; 4+ messages in thread From: Richard Huxton @ 2005-02-28 09:34 UTC (permalink / raw) To: mauro <bertolima@yahoo.it>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-28 09:34 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-02-23 10:19 Postgres 8 - problem: invalid input syntax for integer mauro <bertolima@yahoo.it> 2005-02-24 08:14 ` Richard Huxton <dev@archonet.com> 2005-02-24 15:04 ` mauro <bertolima@yahoo.it> 2005-02-28 09:34 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox