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_tweek@yahoo.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@svana.org> http://svana.org/kleptog/
Show quoted text
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@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?
--
#======================================================================#
# 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