Re: Migration - not null default '0' -> not null default 0 - confused - Mailing list pgsql-general

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?
Writea script to alter those tables? 
Any suggestions?

Mary Wang


-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@gmail.com]
Sent: Tuesday, March 30, 2010 4:32 PM
To: pgsql-general@postgresql.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.klaver@gmail.com