Re: Migration - not null default '0' -> not null default 0 - confused

Steve Atkins <steve(at)blighty(dot)com> writes:
> On Mar 30, 2010, at 4:23 PM, Wang, Mary Y wrote:
>> Now, the source code doesn't work any more. Here is the SQL - INSERT INTO activity_log (day,hour,group_id,browser,ver,platform,time,page,type,user_id) VALUES (20100330,'16','','MOZILLA','5.0','Win','1269990036','/index.php','0','');
>> and pgsql returned "ERROR: invalid input syntax for integer: """.

> You're trying to insert an empty string into group_id. An empty string is not a valid integer.

>> My understanding is that if the value is null, then it should set both the group_id=0 and user_id=0.

> No, that's not the case. You can't insert a null into a not-null field. Also, you're not trying to insert a null unto group_id, you're trying to insert an empty string.

>> But it didn't do it. With the old table, this SQL statement would work.

> I don't think it did. Maybe you changed something else at the same time?

Mary's the one who's trying to port forward from some neolithic PG
version. A bit of experimentation shows that this did work (the integer
input routine would accept an empty string as meaning zero) up through
PG 7.2. Nothing to do with casting, just with the strictness of the
data type's input function.

regards, tom lane