From: | Stef <stef(at)ummon(dot)com> |
---|---|
To: | Keith Worthington <keithw(at)narrowpathinc(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Numeric and CSV under 8.0.1 ? |
Date: | 2005-04-08 18:25:13 |
Message-ID: | 20050408182513.GW30669@survivor.hades |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-04-08 18:39:39 | Re: Numeric and CSV under 8.0.1 ? |
Previous Message | Keith Worthington | 2005-04-08 18:12:11 | Re: Numeric and CSV under 8.0.1 ? |