Possible to insert quoted null value into integer field?

[Pablo S]

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

[Jerry Sievers]

pa*********@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/

[Martijn van Oosterhout]

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 <kl*****@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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBLUyCY5Twig3Ge+YRApw1AJ4+lqRpXcdOPXA6q23pSV Ew5gKrHgCgzAGI
mq64EH4UCZ3YkKZaklgKqkg=
=TzYW
-----END PGP SIGNATURE-----

[gnari]

"Pablo S" <pa*********@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Jan Wieck]

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

"Pablo S" <pa*********@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. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Gaetano Mendola]

Jan Wieck wrote:

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

"Pablo S" <pa*********@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

[Jan Wieck]

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

Jan Wieck wrote:

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

"Pablo S" <pa*********@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. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Gaetano Mendola]

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" <pa*********@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

[Jeff Boes]

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 <>< jb***@qtm.net