Date: Fri, 8 Apr 2005 14:25:13 -0400
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 ?
Message-ID: <20050408182513.GW30669@survivor.hades>
References: <20050408165147.GV30669@survivor.hades>
	<20050408181148.M49002@narrowpathinc.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="8bLSEqNM5P+Jqlqx"
Content-Disposition: inline
In-Reply-To: <20050408181148.M49002@narrowpathinc.com>
User-Agent: Mutt/1.5.6i


--8bLSEqNM5P+Jqlqx
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable


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 ;)
> >=20
> > 	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.
> >=20
> > 	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' ?
> >=20
> >                     Table "public.visitor_main"
> >       Column      |         Type         |          Modifiers
> >=20
> > ------------------+----------------------+------------------------------
> >  iuserid          | numeric(12,0)        | not null
> >  firstname        | character(25)        | not null
> >  lastname         | character(25)        | not null
> >=20
> > Sample Data line
> > '3236','Alonzo','Peter'
> >=20
> > ERROR:  invalid input syntax for type numeric: "'3236'"
> > CONTEXT:  COPY visitor_main, line 1, column iuserid: "'3236'"
> >=20
> > 	Thoughts ?
> > 	Regards
> > 	Steph
>=20
> Steph,
>=20
> '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.
>=20
> HTH
>=20
> Kind Regards,
> Keith
>=20

--8bLSEqNM5P+Jqlqx
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQFCVsyJjI9jiT2RxJQRAuMYAKCIpuH2P9dUIH68gd4LY6kGWQOWyACaA0uS
5Uzx4ZHWWZzLFsR8eC8/u1w=
=GwCd
-----END PGP SIGNATURE-----

--8bLSEqNM5P+Jqlqx--