Обсуждение: Null vs. Empty String in Postgres 8.3.8

Can someone provide a link to the Postgres 8.x documentation that provides information about Null vs. Empty String?
Here is the situation:
Currently,  the source code performs the following SQL statement :
UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND group_id='438';
(This SQL statement worked in a very old version of Postgres)
and pgsql 8.3.8 returned ERROR:  invalid input syntax for integer: ""
The table
 \d user_group
                                     Table "public.user_group"
      Column      |     Type      |                            Modifiers
------------------+---------------+--------------------------------------------
---------------------
 user_group_id    | integer       | not null default nextval(('user_group_pk_se
q'::text)::regclass)
 user_id          | integer       | not null default 0
 group_id         | integer       | not null default 0
 subversion_flags | integer       | not null default 0
.
.

I know probably the best way is to the put check in the application level (making sure that subversion_flags has a
value)before the actual update SQL call; however, I really just want to port the code to work with Postgres 8.3.8
avoidingany code changes if that's possible.   Is there something that I can do at the database level (like alter the
table)so that I can still use the same SQL statement in the application level listed above and it wouldn't return an
error?Any ideas? 

Thanks in advance
Mary

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> Can someone provide a link to the Postgres 8.x documentation that provides information about Null vs. Empty String?
> Here is the situation:
> Currently,  the source code performs the following SQL statement :
> UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND group_id='438';
> (This SQL statement worked in a very old version of Postgres)
> and pgsql 8.3.8 returned ERROR:  invalid input syntax for integer: ""

You were not getting a NULL there.  You were getting an integer zero,
as a result of sloppy input checking in the integer-input routine.

            regards, tom lane

I still don't get it.  I do want a zero for the subversion_flags to be stored in the table.  But it returned an error
becauseit didn't like subversion_flags='' in the UPDATE SQL statement.   

subversion_flags | integer       | not null default 0

Mary


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, April 04, 2010 7:50 PM
To: Wang, Mary Y
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8

"Wang, Mary Y" <mary.y.wang@boeing.com> writes:
> Can someone provide a link to the Postgres 8.x documentation that provides information about Null vs. Empty String?
> Here is the situation:
> Currently,  the source code performs the following SQL statement :
> UPDATE user_group set subversion_flags='' WHERE user_id='5818' AND
> group_id='438'; (This SQL statement worked in a very old version of
> Postgres) and pgsql 8.3.8 returned ERROR:  invalid input syntax for integer: ""

You were not getting a NULL there.  You were getting an integer zero, as a result of sloppy input checking in the
integer-inputroutine. 

            regards, tom lane

On Sun, Apr 4, 2010 at 9:03 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> I still don't get it.  I do want a zero for the subversion_flags to be stored in the table.  But it returned an error
becauseit didn't like subversion_flags='' in the UPDATE SQL statement. 
>
> subversion_flags | integer       | not null default 0

Right. '' is not 0.  the old version of pgsql converted '' to 0 for
you, incorrectly.  Now if you want 0 you need to say 0.

On Sun, Apr 4, 2010 at 10:23 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Sun, Apr 4, 2010 at 9:03 PM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
>> I still don't get it.  I do want a zero for the subversion_flags to be stored in the table.  But it returned an
errorbecause it didn't like subversion_flags='' in the UPDATE SQL statement. 
>>
>> subversion_flags | integer       | not null default 0
>
> Right. '' is not 0.  the old version of pgsql converted '' to 0 for
> you, incorrectly.  Now if you want 0 you need to say 0.
>

Or, since you have the default, set it to null.... (Which may be what
you thought you where doing?)

--
Peter Hunsberger

On Sun, Apr 04, 2010 at 08:03:13PM -0700, Wang, Mary Y wrote:
> I still don't get it.  I do want a zero for the subversion_flags to be stored in the table.  But it returned an error
becauseit didn't like subversion_flags='' in the UPDATE SQL statement.   
>
> subversion_flags | integer       | not null default 0

Thde default will not apply because you attempted to input a value.
Unless I'm mistaken the provision of any value (erroneous or otherwise)
obviates the activation of the default value. There is an exception
to this and that is using the DEFAULT keyword (ie subversion_flags=DEFAULT).

Otherwise the only way it activates is if you leave subversion_flags out
totally.

If you want input data mangling then a TRIGGER may be the way to go.

--
  "A search of his car uncovered pornography, a homemade sex aid, women's
  stockings and a Jack Russell terrier."
    - http://www.news.com.au/story/0%2C27574%2C24675808-421%2C00.html

Peter Hunsberger <peter.hunsberger@gmail.com> wrote:

>>> I still don't get it.  I do want a zero for the subversion_flags to be stored in the table.  But it returned an
errorbecause it didn't like subversion_flags='' in the UPDATE SQL statement. 

>>> subversion_flags | integer       | not null default 0

>> Right. '' is not 0.  the old version of pgsql converted '' to 0 for
>> you, incorrectly.  Now if you want 0 you need to say 0.

> Or, since you have the default, set it to null.... (Which may be what
> you thought you where doing?)

Setting it to NULL does not set it to the default value. You
have to use the keyword DEFAULT for that.

Tim