Skip site navigation (1) Skip section navigation (2)

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

From: Jan Wieck <JanWieck(at)Yahoo(dot)com>
To: gnari <gnari(at)simnet(dot)is>
Cc: Pablo S <pablo_tweek(at)Yahoo(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Possible to insert quoted null value into integer field?
Date: 2004-08-26 20:24:01
Message-ID: 412E46E1.40702@Yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
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 #

In response to

Responses

pgsql-general by date

Next:From: Joe ConwayDate: 2004-08-26 20:33:53
Subject: Re: R: R: space taken by a row & compressed data
Previous:From: Greg StarkDate: 2004-08-26 20:13:03
Subject: Re: R: R: space taken by a row & compressed data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group