Hi all, I have search high and low on this - 

Take for instance the statement : 

insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');

On Pg 7.2.x, the db would happily insert the null val into the int
field.  HOWSOMEVER, 7.4.x will explode and error back with:

"DBD::Pg::st execute failed: ERROR: invalid input syntax for integer:
"" at /cgi-bin/foo line xxx"

I see what it is complaining about but I am wondering if there is any
way to roll back this feature, as I have this cheesy bit of perl that
dynamically builds the query and quotes everything and I don't want to
have to change all occurrences to insert the NULL or worse yet try to
differentiate between int/string.

Many thanks,

P

pablo_tweek(at)yahoo(dot)com (Pablo S) writes:

> Hi all, I have search high and low on this - 
> Take for instance the statement : 
> 
> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');

Have a look at nullif();

create table foo (a int)
;

insert into foo
values (nullif('$varWhichMayBeEmptyString', '')::int)
;

As I recall, there is a comment in the release notes somewhere between
your PG version and the current one, that int cols no longer take
empty string as NULL.

HTH

-- 
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     WWW ECommerce Consultant
                305 321-1144 (mobile	http://www.JerrySievers.com/

On Tue, Aug 24, 2004 at 08:23:14AM -0400, Jerry Sievers wrote:
> As I recall, there is a comment in the release notes somewhere between
> your PG version and the current one, that int cols no longer take
> empty string as NULL.

IIRC, an empty string was interpreted as a zero, never as a NULL.
It is now (rightfully) rejected...
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)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.

"Pablo S" <pablo_tweek(at)yahoo(dot)com> wrote:


> Hi all, I have search high and low on this - 
> 
> Take for instance the statement : 
> 
> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');
> 
> On Pg 7.2.x, the db would happily insert the null val into the int
> field.  HOWSOMEVER, 7.4.x will explode and error back with:
> 
> "DBD::Pg::st execute failed: ERROR: invalid input syntax for integer:
> "" at /cgi-bin/foo line xxx"
> 
> I see what it is complaining about but I am wondering if there is any
> way to roll back this feature, as I have this cheesy bit of perl that
> dynamically builds the query and quotes everything and I don't want to
> have to change all occurrences to insert the NULL or worse yet try to
> differentiate between int/string.

if all else fails, you might use a view mirroring the original
table, but with int1 defined as varchar, with rules handling the
conversion at insert/update.

gnari

On 8/26/2004 4:27 AM, gnari wrote:

> "Pablo S" <pablo_tweek(at)yahoo(dot)com> wrote:
> 
> 
>> Hi all, I have search high and low on this - 
>> 
>> Take for instance the statement : 
>> 
>> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');
>> 
>> On Pg 7.2.x, the db would happily insert the null val into the int
>> field.  HOWSOMEVER, 7.4.x will explode and error back with:

You aren't inserting an SQL NULL value. You try to insert an empty 
string, which is not a valid integer representation.


Jan

>> 
>> "DBD::Pg::st execute failed: ERROR: invalid input syntax for integer:
>> "" at /cgi-bin/foo line xxx"
>> 
>> I see what it is complaining about but I am wondering if there is any
>> way to roll back this feature, as I have this cheesy bit of perl that
>> dynamically builds the query and quotes everything and I don't want to
>> have to change all occurrences to insert the NULL or worse yet try to
>> differentiate between int/string.
> 
> if all else fails, you might use a view mirroring the original
> table, but with int1 defined as varchar, with rules handling the
> conversion at insert/update.
> 
> gnari
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
>                http://archives.postgresql.org


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:

> On 8/26/2004 4:27 AM, gnari wrote:
> 
>> "Pablo S" <pablo_tweek(at)yahoo(dot)com> wrote:
>>
>>
>>> Hi all, I have search high and low on this -
>>> Take for instance the statement :
>>> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');
>>>
>>> On Pg 7.2.x, the db would happily insert the null val into the int
>>> field.  HOWSOMEVER, 7.4.x will explode and error back with:
> 
> 
> You aren't inserting an SQL NULL value. You try to insert an empty 
> string, which is not a valid integer representation.
> 
> 
> Jan

And IIRC for oracle an empty string is a NULL value  :-(


Regards
Gaetano Mendola

On 8/26/2004 5:33 PM, Gaetano Mendola wrote:

> Jan Wieck wrote:
> 
>> On 8/26/2004 4:27 AM, gnari wrote:
>> 
>>> "Pablo S" <pablo_tweek(at)yahoo(dot)com> wrote:
>>>
>>>
>>>> Hi all, I have search high and low on this -
>>>> Take for instance the statement :
>>>> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');
>>>>
>>>> On Pg 7.2.x, the db would happily insert the null val into the int
>>>> field.  HOWSOMEVER, 7.4.x will explode and error back with:
>> 
>> 
>> You aren't inserting an SQL NULL value. You try to insert an empty 
>> string, which is not a valid integer representation.
>> 
>> 
>> Jan
> 
> And IIRC for oracle an empty string is a NULL value  :-(

Who cares about Oracle? They are different things in the ANSI standard.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:

> On 8/26/2004 5:33 PM, Gaetano Mendola wrote:
> 
>> Jan Wieck wrote:
>>
>>> On 8/26/2004 4:27 AM, gnari wrote:
>>>
>>>> "Pablo S" <pablo_tweek(at)yahoo(dot)com> wrote:
>>>>
>>>>
>>>>> Hi all, I have search high and low on this -
>>>>> Take for instance the statement :
>>>>> insert into foo (text1, text2, int1) values ('Foo', 'Bar', '');
>>>>>
>>>>> On Pg 7.2.x, the db would happily insert the null val into the int
>>>>> field.  HOWSOMEVER, 7.4.x will explode and error back with:
>>>
>>>
>>>
>>> You aren't inserting an SQL NULL value. You try to insert an empty 
>>> string, which is not a valid integer representation.
>>>
>>>
>>> Jan
>>
>>
>> And IIRC for oracle an empty string is a NULL value  :-(
> 
> 
> Who cares about Oracle? They are different things in the ANSI standard.

:-(
^^^


Regards
Gaetano Mendola

Gaetano Mendola wrote:
> 
>>> And IIRC for oracle an empty string is a NULL value  :-(
>>
>>
>>
>> Who cares about Oracle? They are different things in the ANSI standard.
> 
> 
> :-(
> ^^^
> 

Seems like you could handle this with a rule:

create rule as on insert to my_table
where new.that_column = '' do instead
insert into my_table (col_a, col_b, that_col)
values (new.col_a, new.col_b, NULL);

Or would this break long before the rule got involved, because 
new.that_column has a bad value?

-- 
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jboes(at)qtm(dot)net