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

On Mar 30, 2010, at 4:23 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: """.

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?

abacus=> create table foo (bar integer not null default '0');
CREATE TABLE
abacus=> insert into foo (bar) values ('');
ERROR: invalid input syntax for integer: ""

>
> Any suggestions on what I need to do for the not null default values?

It's nothing at all to do with them, I don't think - it's just that you're trying to insert bad data into the table.

You can either use the literal string "default" (with no quotes) to insert the default value into a field, or don't list the field in the list of fields to insert at all.

Cheers,
Steve