Обсуждение: 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