From: Keith Worthington <keithw@narrowpathinc.com>
To: Stef <stef@ummon.com>
To: pgsql-sql@postgresql.org
Subject: Re: Numeric and CSV under 8.0.1 ?
Date: Fri, 8 Apr 2005 14:12:11 -0400
Message-ID: <20050408181148.M49002@narrowpathinc.com> (raw)
In-Reply-To: <20050408165147.GV30669@survivor.hades>
References: <20050408165147.GV30669@survivor.hades>
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: keithw@narrowpathinc.com, stef@ummon.com
Subject: Re: Numeric and CSV under 8.0.1 ?
In-Reply-To: <20050408181148.M49002@narrowpathinc.com>
* 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