Thread: conversion
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;
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;
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 SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . co . nzCNOTES 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 <<--
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;
>
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
> <<--