Thread: pg_atoi error
Hello, In my query: select * from experimentinfo where expid='1'; here data type of expid is int. My question is: if I change expid ='xyz', error is: pg_atoi: error in "xyz": can't parse "xyz" if I change expid ='', error is: pg_atoi: zero-length string In mysql, seems this will not happen and will give you 0 row result. but not error message. So, How can I avoid these annoying message? Regards, William
On Fri, Jan 23, 2004 at 15:01:15 +0000, LIANHE SHAO <lshao2@jhmi.edu> wrote: > Hello, > > In my query: > select * from experimentinfo where expid='1'; > here data type of expid is int. > My question is: > if I change expid ='xyz', error is: pg_atoi: error > in "xyz": can't parse "xyz" > if I change expid ='', error is: pg_atoi: > zero-length string > > In mysql, seems this will not happen and will give > you 0 row result. but not error message. If you want to shoot yourself in the foot go use mysql. > > So, How can I avoid these annoying message? You can either have your application handle obviously bogus data or you can write a function in postgres that converts text to int using whatever rules you want for assigning values to nonnumeric strings.
Please keep threads on the mailing list. On Fri, Jan 23, 2004 at 15:32:47 +0000, LIANHE SHAO <lshao2@jhmi.edu> wrote: > > > > So, How can I avoid these annoying message? > > > > You can either have your application handle > obviously bogus data or > > you can write a function in postgres that converts > text to int using > > whatever rules you want for assigning values to > nonnumeric strings. > > > > The above query is simple, but my query sometimes is > kind of complex. If I have to manually deal with > each possible bogus data or use functions to convert > each string to number, beside lot of overhead, maybe > there are somethings you still forget to do. Seems > it does not solve the problem completely. This doesn't make sense. If you want to treat all garbage data as being the same as 0, just write a function to do it. If you don't know what to do with the garbage data, how do you expect Postgres to do the right thing on its own.
> Please keep threads on the mailing list. > > On Fri, Jan 23, 2004 at 15:32:47 +0000, > LIANHE SHAO <lshao2@jhmi.edu> wrote: > > > > > > So, How can I avoid these annoying message? > > > > > > You can either have your application handle > > obviously bogus data or > > > you can write a function in postgres that converts > > text to int using > > > whatever rules you want for assigning values to > > nonnumeric strings. > > > > > > > The above query is simple, but my query sometimes is > > kind of complex. If I have to manually deal with > > each possible bogus data or use functions to convert > > each string to number, beside lot of overhead, maybe > > there are somethings you still forget to do. Seems > > it does not solve the problem completely. > > This doesn't make sense. If you want to treat all garbage data as > beingthe same as 0, just write a function to do it. If you don't > know what to > do with the garbage data, how do you expect Postgres to do the > right thing > on its own. > Seems you missed my point. Actually I am wondering if pgsql can work on its own with garbage data like mysql. for example if it should be expid ='1' but I use it with expid='xyz'. In this case, MySql will seliently give you 0 row instead of error message. From your point, Seems Pgsql will only give error message unless you deal with it manually. Regards, William
On Fri, Jan 23, 2004 at 17:34:39 +0000, LIANHE SHAO <lshao2@jhmi.edu> wrote: > > Seems you missed my point. > Actually I am wondering if pgsql can work on its own > with garbage data like mysql. for example if it > should be expid ='1' but I use it with expid='xyz'. > In this case, MySql will seliently give you 0 row > instead of error message. From your point, Seems > Pgsql will only give error message unless you deal > with it manually. Why do you think Postgres should magically pick 0 out of all of the possible numbers to use? If you want garbage to be treated as 0 you can write a function to do it. If you use this function to create a cast from text to int this might even work without you having to call the function around the questionable data. I am not absolutely sure that unknown will go to int through text in preference to directly to int though. If in the real case your data isn't qouted strings but text data already in a table, then it should work.
LIANHE SHAO <lshao2@jhmi.edu> writes:
> Actually I am wondering if pgsql can work on its own
> with garbage data like mysql. for example if it
> should be expid ='1' but I use it with expid='xyz'.
> In this case, MySql will seliently give you 0 row
> instead of error message.
This is a fundamental difference in philosophy between the two projects.
You are wasting your breath trying to convince any Postgres developer
that this aspect of MySQL's behavior is a wise design. And I'm sure you
would get equally much push-back from the MySQL developers if you tried
to persuade them to tighten their error checking. If MySQL's philosophy
agrees with your worldview, then you should probably go use MySQL.
Alternatively, you can write your own conversion function that does what
you want, as several people have suggested already. You can even cause
it to become the default behavior in your installation (just change the
input function for type int4). But it's not going to become the
standard behavior for Postgres.
regards, tom lane
On Fri, 23 Jan 2004 12:59:15 -0500 Tom Lane <tgl@sss.pgh.pa.us> threw this fish to the penguins: > LIANHE SHAO <lshao2@jhmi.edu> writes: > > Actually I am wondering if pgsql can work on its own > > with garbage data like mysql. for example if it > > should be expid ='1' but I use it with expid='xyz'. > > In this case, MySql will seliently give you 0 row > > instead of error message. > > This is a fundamental difference in philosophy between the two projects. > You are wasting your breath trying to convince any Postgres developer > that this aspect of MySQL's behavior is a wise design. And I'm sure you > would get equally much push-back from the MySQL developers if you tried > to persuade them to tighten their error checking. If MySQL's philosophy > agrees with your worldview, then you should probably go use MySQL. > > Alternatively, you can write your own conversion function that does what > you want, as several people have suggested already. You can even cause > it to become the default behavior in your installation (just change the > input function for type int4). But it's not going to become the > standard behavior for Postgres. > > regards, tom lane Philosophy aside, I would object strongly to postgres quietly accepting garbage as a zero. If some application is feeding a string value to the wrong field, I certainly want it to fail, with clanging bells and crashing the app (or at least generating an exception...). Otherwise leads to data corruption that could be dangerous and very difficult to unravel. It's the application's job to validate data, not postgres. -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)