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

Ok. Thanks. In that case, I'm going to have a lot of type casting issues. What's the best way to fix all tables? Write a script to alter those tables?
Any suggestions?

Mary Wang

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)gmail(dot)com]
Sent: Tuesday, March 30, 2010 4:32 PM
To: pgsql-general(at)postgresql(dot)org
Cc: Wang, Mary Y
Subject: Re: [GENERAL] Migration - not null default '0' -> not null default 0 - confused

On Tuesday 30 March 2010 4:23:39 pm Wang, Mary Y wrote:
> Hi,
>
> I'm confused. I'm in the process of migrating to 8.3.8. I used
> pg_dump and pg_restore command for migration. Here is my problem.
> Here is my old table prior migration:
> \d activity_log
> Table "activity_log"
> Attribute | Type | Modifier
> -----------+----------------------+--------------------------
> day | integer | not null default '0'
> hour | integer | not null default '0'
> group_id | integer | not null default '0'
> browser | character varying(8) | not null default 'OTHER'
> ver | double precision | not null default '0.00'
> platform | character varying(8) | not null default 'OTHER'
> time | integer | not null default '0'
> page | text |
> type | integer | not null default '0'
> user_id | integer | not null default '0'
>
>
> Here is my table after migration:
> \d activity_log;
> Table "public.activity_log"
> Column | Type | Modifiers
> ----------+----------------------+------------------------------------
> ----------+----------------------+-----
>---- day | integer | not null default 0
> hour | integer | not null default 0
> group_id | integer | not null default 0
> browser | character varying(8) | not null default 'OTHER'::character
> varying ver | double precision | not null default 0::double
> precision platform | character varying(8) | not null default
> 'OTHER'::character varying time | integer | not null
> default 0
> page | text |
> type | integer | not null default 0
> user_id | integer | not null default 0
>
> 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:
> """. My understanding is that if the value is null, then it should
> set both the
> group_id=0 and user_id=0. But it didn't do it. With the old table, this
> SQL statement would work.
>
> Any suggestions on what I need to do for the not null default values?
>
> I'm running on Postgres 8.3.8 and RHEL 3.9.
>
> Thanks
> Mary Wang

8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an integer i.e '0'::integer.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com