Обсуждение: Problem with void integer

I have a big problem to jump from 7.2 to 8.0.3
In my application I was doing a major use of int and float
And the difference between '0' and void NULL '' data content.

Now it seems to me that void is not allowed for an integer ?

What should I do ?

On Mon, May 30, 2005 at 03:46:09PM +0200, Alexandre Lollini wrote:
> I have a big problem to jump from 7.2 to 8.0.3
> In my application I was doing a major use of int and float
> And the difference between '0' and void NULL '' data content.
>
> Now it seems to me that void is not allowed for an integer ?

Ok, NULL, 0 (number zero) and '' (empty string) are all distinct.

Oracle considers NULL and <empty string> to be the same, which causes
confusion but that is not the problem here.

In older versions of PostgreSQL, an empty string could be converted to
an integer and would result in 0. This is contrary to SQL spec and was
changed somewhere after 7.2. The release notes from back then should
mention it somewhere.

Also, PostgreSQL no longer freely translates between integer and float,
you have to explicitly ask for a conversion that loses precision.

> What should I do ?

Some versions had a hack to fix this, but I don't think 8.0 has that
anymore... If you need more help, you need to post the query and exact
error message.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Please reply to the list so everyone can follow the discussion:

On Mon, May 30, 2005 at 07:50:03PM +0200, Alexandre Lollini wrote:
> Here is my query sample (exact):

<snip>

> INSERT INTO sometable ( someint4field ) VALUES ( '' );
>
> To work.

Sorry, an empty string is not an integer. If you want NULL, say NULL.

> Or find me another syntax to insert '' in an integer field.
> With POSTGRESQL 803
>
> What I do not understand is why, when I "/i dump.txt" the data from my 7.2
> dump
> All the void integers '' where set correctly.

What do you mean? In 7.2 there is no integer displayed as '', only 0
and NULL. That '' used to convert to zero is *wrong* and won't be
changed back, sorry...

> Now at run time impossible to insert a void integer.

What is a "void integer"? You have either NULL or 0, there have never
been any other choices.

> For the moment I have modified all the application to convert void to zero
> prior to insert/update
>
> This is NOT the expected behavior, but, I am forced to, to preserve run
> time.

This *is* expected behaviour, an empty string is not zero, end of
story.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.