Re: Null vs. Empty String in Postgres 8.3.8

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 because it 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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Sunday, April 04, 2010 7:50 PM
To: Wang, Mary Y
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Null vs. Empty String in Postgres 8.3.8

"Wang, Mary Y" <mary(dot)y(dot)wang(at)boeing(dot)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