From: Stef <stef@ummon.com>
To: Keith Worthington <keithw@narrowpathinc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Numeric and CSV under 8.0.1 ?
Date: Fri, 8 Apr 2005 14:25:13 -0400
Message-ID: <20050408182513.GW30669@survivor.hades> (raw)
In-Reply-To: <20050408181148.M49002@narrowpathinc.com>
References: <20050408165147.GV30669@survivor.hades>
<20050408181148.M49002@narrowpathinc.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stef@ummon.com, keithw@narrowpathinc.com
Subject: Re: Numeric and CSV under 8.0.1 ?
In-Reply-To: <20050408182513.GW30669@survivor.hades>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox