Thread: Possible to insert quoted null value into integer field?
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 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
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 #
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
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 #
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
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