Re: [GENERAL] 7.3 -> pg_atoi: zero-length string

I don't think many will be caught-out, since it's an overwhelmingly
daft thing to do anyway!

If you've got a numeric type column then assign numeric values to, not
strings. Or explicitly convert.

I'm sure all occurrences of this "in the wild" are due to sloppy
SQL...

For the record:

 lkind@coulin:~% sql -uingres iidbdb
 INGRES TERMINAL MONITOR Copyright (c) 1981, 1997 Computer Associates Intl, Inc.
 OpenIngres SPARC SOLARIS Version OI 2.0/9712 (su4.us5/00) login
 Tue Dec  3 16:17:27 2002

 continue
 * create table test(f1 int);\g
 Executing . . .

 continue
 * insert into test(f1) values('');\g
 Executing . . .

 E_US0B61 line 1, You cannot assign  a value of type 'varchar' to a column
     of type 'integer'. Explicitly convert the value to the required type.
     (Tue Dec  3 16:18:22 2002)
 continue

 * select int4('');\g
 Executing . . .
  col1
  0
 (1 row)
 continue

Lee.

Ben-Nes Michael writes:
 > Then Why not set it to NULL
 >
 > Seems logic as there is nothing between ''
 >
 > What is the solution of other dbs ( oracle, db2 .. ) to:
 > insert into table (num) value (''); ?
 >
 > Who knows how many application will suffer becouse of this.
 >
 > ----- Original Message -----
 > From: "Bruce Momjian" <pgman@candle.pha.pa.us>
 > To: "Lee Kindness" <lkindness@csl.co.uk>
 > Cc: "Ben-Nes Michael" <miki@canaan.co.il>; "postgresql"
 > <pgsql-general@postgresql.org>; <pgsql-hackers@postgresql.org>
 > Sent: Tuesday, December 03, 2002 6:08 PM
 > Subject: Re: [GENERAL] 7.3 -> pg_atoi: zero-length string
 >
 > > The change was made to tighten up the code to catch errors sooner.
 > > There isn't much logic to making '' be 0, and no one could make a case
 > > for keeping such a mapping.
 > >
 > > --------------------------------------------------------------------------
 > -
 > >
 > > Lee Kindness wrote:
 > > > Was it necessary? No idea, you're welcome to search through the
 > > > pgsql-hackers archives to determine the reasoning behind the change. I
 > > > believe the change was made by Bruce Momjian (going by the release
 > > > notes). I only remember reading the discussion in passing.
 > > >
 > > > This is also one of the reasons for beta releases - to allow people to
 > > > test against the new version and pick up these sort of things. Either
 > > > this gives them/you time to make changes, or to lobby to get the old
 > > > behaviour back.
 > > >
 > > > If I were in your situation i'd probably hack back the old behaviour
 > > > to 7.3, compile and run that while changes were made. Or stick with
 > > > 7.2.x until changes were made to your applications, have you got a
 > > > 'big carrot' for going with 7.3?
 > > >
 > > > Lee.
 > > >
 > > > Ben-Nes Michael writes:
 > > >  > That's indeed very nice but I don't see the logic in it.
 > > >  >
 > > >  > If I want to upgrade I need to go on all my projects and change
 > thousands of
 > > >  > lines.
 > > >  >
 > > >  > And that's not all :(
 > > >  > I have other applications like phprojekt that was not developed by me
 > and
 > > >  > became useless now as I cant insert.
 > > >  >
 > > >  > Was this step so necessary ?
 > > >  >
 > > >  > ----- Original Message -----
 > > >  > From: "Lee Kindness" <lkindness@csl.co.uk>
 > > >  > To: "Ben-Nes Michael" <miki@canaan.co.il>
 > > >  > Cc: "postgresql" <pgsql-general@postgresql.org>; "Lee Kindness"
 > > >  > <lkindness@csl.co.uk>
 > > >  > Sent: Tuesday, December 03, 2002 12:37 PM
 > > >  > Subject: [GENERAL] 7.3 -> pg_atoi: zero-length string
 > > >  >
 > > >  > > Hi, one of the changes in 7.3 was to disallow '' being implicitly
 > > >  > > converted to 0. In your example below image_order is clearly not a
 > > >  > > text/char column, so what are you trying to set it too? If you want
 > it
 > > >  > > be 0 then explicitly use 0, if you want it to be undefined then use
 > > >  > > NULL.
 > > >  > >
 > > >  > > Lee.
 > > >  > >
 > > >  > > Ben-Nes Michael writes:
 > > >  > >  > Hi
 > > >  > >  >
 > > >  > >  > After I upgraded 7.2.3 to 7.3 I started to get the following
 > errors:
 > > >  > >  > pg_atoi: zero-length string
 > > >  > >  >
 > > >  > >  > its seems that i get it when not all field have content:
 > > >  > >  >
 > > >  > >  > this is one example that generate the error:
 > > >  > >  > insert into images (section_id, pic_date, image_order) values
 > ('8',
 > > >  > >  > '2002-12-03', '')
 > > >
 > >