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

From: "Wang, Mary Y" <mary(dot)y(dot)wang(at)boeing(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Migration - not null default '0' -> not null default 0 - confused
Date: 2010-03-30 23:23:39
Message-ID: FA20D4C4FEBFD148B1C0CB09913825FC0457D92D64@XCH-SW-06V.sw.nos.boeing.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2010-03-30 23:32:11 Re: Migration - not null default '0' -> not null default 0 - confused
Previous Message Jeff Davis 2010-03-30 22:52:48 Re: pgfoundry registration