Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6EB5064FCF8 for ; Wed, 10 Sep 2008 16:32:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67916-10 for ; Wed, 10 Sep 2008 16:32:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from server.firetrust.net (mailserver.firetrust.com [209.213.221.13]) by postgresql.org (Postfix) with ESMTP id 555E164FCCD for ; Wed, 10 Sep 2008 16:32:12 -0300 (ADT) Received: from localhost.localdomain ([192.168.1.247]) (authenticated bits=0) by server.firetrust.net (8.14.3/8.14.3) with ESMTP id m8AJW69W023584 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 11 Sep 2008 07:32:08 +1200 Date: Thu, 11 Sep 2008 07:31:07 +1200 From: Steve Holdoway To: pgsql-admin@postgresql.org Subject: Re: [GENERAL] FW: How to upload data to postgres Message-Id: <20080911073107.c9223a20.steve.holdoway@firetrust.com> In-Reply-To: <6D8C7E015447D0428D80E9DBABA06A1E03F4ABE6@S0-OTT-X2.nrn.nrcan.gc.ca> References: <6D8C7E015447D0428D80E9DBABA06A1E03F4AA5C@S0-OTT-X2.nrn.nrcan.gc.ca> <200809091943.13745.aklaver@comcast.net> <6D8C7E015447D0428D80E9DBABA06A1E03F4AB76@S0-OTT-X2.nrn.nrcan.gc.ca> <200809100738.57156.aklaver@comcast.net> <6D8C7E015447D0428D80E9DBABA06A1E03F4ABE6@S0-OTT-X2.nrn.nrcan.gc.ca> Organization: Firetrust Ltd X-Mailer: Sylpheed 2.4.8 (GTK+ 2.12.9; x86_64-pc-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="PGP-SHA1"; boundary="Signature=_Thu__11_Sep_2008_07_31_07_+1200_tFODX7hU0q2y=rOV" X-MailWasher-Server-Scanned: Checked by MailWasher server v2.3.2 (www.Firetrust.com) X-MailWasher-Server-Status: Clean X-Virus-Scanned: ClamAV 0.93.3/8211/Thu Sep 11 07:01:28 2008 on server.firetrust.net X-Virus-Status: Clean X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/81 X-Sequence-Number: 30174 --Signature=_Thu__11_Sep_2008_07_31_07_+1200_tFODX7hU0q2y=rOV Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Why not just export as 'insert into (... ) values (...)' format. If= ingres can't export as such, you can write a select statement to do this.= =20 Slow, sure - but the lowest common denominator. Steve On Wed, 10 Sep 2008 11:27:58 -0400 "Markova, Nina" wrote: > =20 > I also plan to try to export data in XML format (from Ingres) and import > it to Postgres. >=20 > I didn't find any utility for importing XML data into Postgres. Or just > looking at the wrong document? > I run Postgres 8.2.4 >=20 > Thanks, > Nina >=20 > -----Original Message----- > From: Adrian Klaver [mailto:aklaver@comcast.net]=20 > Sent: September 10, 2008 10:39 > To: pgsql-general@postgresql.org > Cc: Markova, Nina; pgsql-admin@postgresql.org > Subject: Re: [GENERAL] FW: How to upload data to postgres >=20 > On Wednesday 10 September 2008 7:14:50 am Markova, Nina wrote: > > Thanks Adrian. > > > > I have read the Postgres 'copy' - the problem is that Postgres doesn't >=20 > > understand Ingres format. This is I think where the failure comes > from. > > If I don't find a tool, I have to write scripts to convert data to=20 > > something postgres understand. > > > > In the Ingres file with data for each varchar field, before the field=20 > > is the real size : > > > > 48070 820010601 820030210 41.890 > > -80.811 0.000 1U > > 3A16 819871030 0 47.471 -70.006 > > 0.015 1R 0 > > > > In the example above: > > 3A16 - means for varchar(5) field there are only characters, i.e. A16 >=20 > > 48070 - means for varchar(5) field there are only 4 characters, i.e. > > 8070 > > 819871030 - 8 characters, i.e. 19871030 >=20 > That would be the problem. The COPY from Postgres does not understand > the metadata associated with the field data and would try to insert the > complete string. I can see three options: > 1) As has been suggested in another other post, export the Ingres data > as data only CSV i.e 'A16' not '3A16' > 2) Your suggestion of cleaning up data via a script. > 3) Create holding table in Postgres that has varchar() fields (varchar > with no length specified) and import into and then do your data cleanup > before moving over to final table. >=20 > > > > When I created the same table in Postgres, inserted some test data =20 > > and later copied it to a file, this is how it looks like: > > > > A16 19871030 47.471 -70.006 0.015 R > > KLNO 19801028 47.473 -70.006 0.016 R > > MLNO 19801028 19990101 47.413 -70.006 0.016 R > > > > Column | Type | Modifiers > > > > -------------+------------------------+------------------------------- > > -------------+------------------------+-- > > ------- > > sta | character varying(5) | not null > > ondate | character varying(8) | not null > > offdate | character varying(8) | not null > > lat | double precision | not null > > lon | double precision | not null > > elev | double precision | not null default 0 > > regist_code | character(1) | not null default ' '::bpchar > > > > > > Nina > > >=20 >=20 >=20 >=20 >=20 > -- > Adrian Klaver > aklaver@comcast.net >=20 > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin --Signature=_Thu__11_Sep_2008_07_31_07_+1200_tFODX7hU0q2y=rOV Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFIyCCxDN07rEXM5z0RAlHtAJ9l8T1NgieUlXoz4Hc1gsvwAK5TqwCbB5aO nW2F/5zfTSd+/R9jHXRNztI= =DK1C -----END PGP SIGNATURE----- --Signature=_Thu__11_Sep_2008_07_31_07_+1200_tFODX7hU0q2y=rOV--