Re: Numeric and CSV under 8.0.1 ?

Hello Keith,well, I can understand that 3265 appears to be a
string, but, I was under the impression that -everything-
in a CSV format file could have ' ' around it? Is this not
the case ?
Sorry if I am being completely insane here :)Steph

On Fri, Apr 08, 2005 at 02:12:11PM -0400, Keith Worthington wrote:
> 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
>