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
>