Re: Numeric and CSV under 8.0.1 ?

On Fri, 8 Apr 2005 12:51:47 -0400, Stef wrote
> Hello Everyone,
>     Currently, here at work, I am doing the whole
> 'advocacy' part of postgreSQL. It's not really hard to
> do, as the other database's are MySQL and Sybase ;)
> 
>     There is obviously a whole spat of data munging
> going on in the background, and I noticed that psql in
> 8.0.1 now accepts CSV ! Great.
> 
>     Except, it doesn't really appear to be 100% there.
> Numeric's wrapped in '...' don't want to appear to go in.
> Is this a 'known problem' ?
> 
>                     Table "public.visitor_main"
>       Column      |         Type         |          Modifiers
> 
> ------------------+----------------------+------------------------------
>  iuserid          | numeric(12,0)        | not null
>  firstname        | character(25)        | not null
>  lastname         | character(25)        | not null
> 
> Sample Data line
> '3236','Alonzo','Peter'
> 
> ERROR:  invalid input syntax for type numeric: "'3236'"
> CONTEXT:  COPY visitor_main, line 1, column iuserid: "'3236'"
> 
>     Thoughts ?
>     Regards
>     Steph

Steph,

'3236' is a string not a numeric.  As I see it (novice that I am) you have
three choices.  1) Write an external program (gawk, sed, etc.) to remove the
quotes around that field.  2) Import the data into an intermediate table and
then using an after trigger move and manipulate the data using CAST.  3)
Import the data into your table using a BEFORE trigger and manipulate the data
using CAST.

HTH

Kind Regards,
Keith