Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-31 Thread Tom Lane 
Steve Atkins st...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-31 Thread Raymond O'Donnell 
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
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Wang, Mary Y 
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





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Adrian Klaver 
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.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Wang, Mary Y 
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


-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@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.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Adrian Klaver 
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.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Jeff Davis 
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Steve Atkins 

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Migration - not null default '0' - not null default 0 - confused

2010-03-30 Thread Adrian Klaver 
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.kla...@gmail.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general