Thread: Possible to insert quoted null value into integer field?

Possible to insert quoted null value into integer field?

From
pablo_tweek@yahoo.com (Pablo S)
Date:
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

Re: Possible to insert quoted null value into integer field?

From
"gnari"
Date:
"Pablo S" <pablo_tweek@yahoo.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



Re: Possible to insert quoted null value into integer field?

From
Jan Wieck
Date:
On 8/26/2004 4:27 AM, gnari wrote:

> "Pablo S" <pablo_tweek@yahoo.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@Yahoo.com #

Re: Possible to insert quoted null value into integer field?

From
Gaetano Mendola
Date:
Jan Wieck wrote:

> On 8/26/2004 4:27 AM, gnari wrote:
>
>> "Pablo S" <pablo_tweek@yahoo.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





Re: Possible to insert quoted null value into integer field?

From
Jan Wieck
Date:
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@yahoo.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@Yahoo.com #

Re: Possible to insert quoted null value into integer field?

From
Gaetano Mendola
Date:
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@yahoo.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




Re: Possible to insert quoted null value into integer field?

From
Jeff Boes
Date:
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@qtm.net