Thread

1. conversion

   Ken Kline <ken@oldbs.com> — 2001-02-26T02:32:06Z

   Hello,
   another brain twister, at least for me...
   
   i have a table of varchar and one of the values I want
   to insert into another table, one of the columns is
   defined as INTEGER in destination table, column...
   and none of these statements seem to work
   
    INSERT INTO pledge_classes (semester, year)
    SELECT pseason, to_number('pyear','9999') from temp;
   
    INSERT INTO pledge_classes (semester, year)
    SELECT pseason, pyear::integer from temp;
   
    INSERT INTO pledge_classes (semester, year)
    SELECT pseason, pyear::numeric(4) from temp;
   
   
   

2. Re: conversion

   Ken Kline <ken@oldbs.com> — 2001-02-26T04:11:22Z

   follow up....
   actually the destination column is defined
   as a numeric(4)
   
   the following are the statements again with there error messages:
   
   SELECT pseason, to_number(pyear,'9999') from temp;
   ERROR: Bad numeric input format  '  '
   
    SELECT pyear::int from temp;
   ERROR: Cannot cast type 'varchar' to 'int4'
   
   
   
   
   Ken Kline wrote:
   
   > Hello,
   > another brain twister, at least for me...
   > i have a table of varchar and one of the values I want
   > to insert into another table, one of the columns is
   > defined as INTEGER in destination table, column...
   > and none of these statements seem to work
   >
   >  INSERT INTO pledge_classes (semester, year)
   >  SELECT pseason, to_number('pyear','9999') from temp;
   >
   >  INSERT INTO pledge_classes (semester, year)
   >  SELECT pseason, pyear::integer from temp;
   >
   >  INSERT INTO pledge_classes (semester, year)
   >  SELECT pseason, pyear::numeric(4) from temp;
   
   
   

3. Re: conversion

   Christopher Sawtell <csawtell@xtra.co.nz> — 2001-02-26T06:51:17Z

   On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
   > follow up....
   > actually the destination column is defined
   > as a numeric(4)
   
   Could you to a \d on each of the tables and tell us the results.
   
   [ ... ]
   
   -- 
   Sincerely etc.,
   
    NAME       Christopher Sawtell
    CELL PHONE 021 257 4451
    ICQ UIN    45863470
    EMAIL      csawtell @ xtra . co . nz
    CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
   
    -->> Please refrain from using HTML or WORD attachments in e-mails to me 
   <<--
   
   
   

4. Re: conversion

   Ken Kline <ken@oldbs.com> — 2001-02-26T16:46:49Z

   here you go, thanks in advance, ken
   
               Table "temp"
    Attribute |    Type     | Modifier
   -----------+-------------+----------
    pseason   | varchar(15) |
    pyear     | varchar(5)  |
   
   adx=# \d pledge+ _classe4s  s
                          Table "pledge_classes"
    Attribute |     Type     |                 Modifier
   -----------+--------------+------------------------------------------
    pc_id     | integer      | not null default nextval('pc_seq'::text)
    semester  | varchar(6)   |
    year      | numeric(4,0) |
    pc_letter | varchar(20)  |
    pc_name   | varchar(50)  |
   Index: pc_pk
   
   adx=# \q
   bash-2.04$
   Script done on Mon Feb 26 11:42:35 2001
   
   
   Christopher Sawtell wrote:
   
   > On Mon, 26 Feb 2001 17:11, Ken Kline wrote:
   > > follow up....
   > > actually the destination column is defined
   > > as a numeric(4)
   >
   > Could you to a \d on each of the tables and tell us the results.
   >
   > [ ... ]
   >
   > --
   > Sincerely etc.,
   >
   >  NAME       Christopher Sawtell
   >  CELL PHONE 021 257 4451
   >  ICQ UIN    45863470
   >  EMAIL      csawtell @ xtra . co . nz
   >  CNOTES     ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
   >
   >  -->> Please refrain from using HTML or WORD attachments in e-mails to me
   > <<--
   
   
   

5. Re: conversion

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-02-26T17:06:57Z

   It looks like you have some rows for pyear which do 
   not convert cleanly into a number like '  '.  What
   do you want it to do in such cases?
   
   On Sun, 25 Feb 2001, Ken Kline wrote:
   
   > follow up....
   > actually the destination column is defined
   > as a numeric(4)
   > 
   > the following are the statements again with there error messages:
   > 
   > SELECT pseason, to_number(pyear,'9999') from temp;
   > ERROR: Bad numeric input format  '  '
   > 
   >  SELECT pyear::int from temp;
   > ERROR: Cannot cast type 'varchar' to 'int4'
   > 
   > 
   > 
   > 
   > Ken Kline wrote:
   > 
   > > Hello,
   > > another brain twister, at least for me...
   > > i have a table of varchar and one of the values I want
   > > to insert into another table, one of the columns is
   > > defined as INTEGER in destination table, column...
   > > and none of these statements seem to work
   > >
   > >  INSERT INTO pledge_classes (semester, year)
   > >  SELECT pseason, to_number('pyear','9999') from temp;
   > >
   > >  INSERT INTO pledge_classes (semester, year)
   > >  SELECT pseason, pyear::integer from temp;
   > >
   > >  INSERT INTO pledge_classes (semester, year)
   > >  SELECT pseason, pyear::numeric(4) from temp;
   >