Thread: Migration - not null default '0' -> not null default 0 - confused
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 itshould 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
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
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
On Tuesday 30 March 2010 4:49:42 pm Wang, Mary Y wrote: > 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? Write a script to alter those > tables? Any suggestions? > > Mary Wang > > This might help: http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html -- Adrian Klaver adrian.klaver@gmail.com
On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote: > 8.3 tightened up type casting. You cannot INSERT a '0' without casting it to an > integer i.e '0'::integer. I don't think that's accurate: postgres=# select version(); version -------------------------------------------------------------------------------------------------------------------- -- PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit (1 row) postgres=# create table ints(i int); CREATE TABLE postgres=# insert into ints values('0'); INSERT 0 1 Regards, Jeff Davis
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
On Tuesday 30 March 2010 4:59:30 pm Jeff Davis wrote: > On Tue, 2010-03-30 at 16:32 -0700, Adrian Klaver wrote: > > 8.3 tightened up type casting. You cannot INSERT a '0' without casting it > > to an integer i.e '0'::integer. > > I don't think that's accurate: > > postgres=# select version(); > > version > > --------------------------------------------------------------------------- >----------------------------------------- -- > PostgreSQL 9.0devel on x86_64-unknown-linux-gnu, compiled by GCC > gcc-4.3.real (Ubuntu 4.3.2-1ubuntu12) 4.3.2, 64-bit > (1 row) > > postgres=# create table ints(i int); > CREATE TABLE > postgres=# insert into ints values('0'); > INSERT 0 1 > > > Regards, > Jeff Davis My mistake. I could of swore I had problems with this when I first tried 8.3. What happens when you rely on memory. -- Adrian Klaver adrian.klaver@gmail.com
Steve Atkins <steve@blighty.com> writes: > On Mar 30, 2010, at 4:23 PM, Wang, Mary Y wrote: >> 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? Mary's the one who's trying to port forward from some neolithic PG version. A bit of experimentation shows that this did work (the integer input routine would accept an empty string as meaning zero) up through PG 7.2. Nothing to do with casting, just with the strictness of the data type's input function. regards, tom lane
On 31/03/2010 15:09, Tom Lane wrote: > Mary's the one who's trying to port forward from some neolithic PG > version. Lots of pain, but hopefully lots of gain too! :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie