Re: 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