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

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
listthe field in the list of fields to insert at all. 

Cheers,
  Steve