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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Fri, Jan 23, 2004 at 15:01:15 +0000,
LIANHE SHAO <lshao2 (AT) jhmi (DOT) edu> wrote:
If you want to shoot yourself in the foot go use mysql.

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Please keep threads on the mailing list.

On Fri, Jan 23, 2004 at 15:32:47 +0000,
LIANHE SHAO <lshao2 (AT) jhmi (DOT) edu> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

On Fri, Jan 23, 2004 at 17:34:39 +0000,
LIANHE SHAO <lshao2 (AT) jhmi (DOT) edu> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

LIANHE SHAO <lshao2 (AT) jhmi (DOT) edu> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Fri, 23 Jan 2004 12:59:15 -0500
Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> threw this fish to the penguins:

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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match