From: Steve Holdoway <steve.holdoway@firetrust.com>
To: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] FW: How to upload data to postgres
Date: Thu, 11 Sep 2008 07:31:07 +1200
Message-ID: <20080911073107.c9223a20.steve.holdoway@firetrust.com> (raw)
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>
Why not just export as 'insert into <table> (... ) values (...)' format. If ingres can't export as such, you can write a select statement to do this.
Slow, sure - but the lowest common denominator.
Steve
On Wed, 10 Sep 2008 11:27:58 -0400
"Markova, Nina" <nmarkova@NRCan.gc.ca> wrote:
>
> I also plan to try to export data in XML format (from Ingres) and import
> it to Postgres.
>
> 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
>
> Thanks,
> Nina
>
> -----Original Message-----
> From: Adrian Klaver [mailto:aklaver@comcast.net]
> 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
>
> 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
>
> > 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
> > something postgres understand.
> >
> > In the Ingres file with data for each varchar field, before the field
> > 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
>
> > 48070 - means for varchar(5) field there are only 4 characters, i.e.
> > 8070
> > 819871030 - 8 characters, i.e. 19871030
>
> 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.
>
> >
> > When I created the same table in Postgres, inserted some test data
> > 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
> >
>
>
>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: steve.holdoway@firetrust.com
Subject: Re: [GENERAL] FW: How to upload data to postgres
In-Reply-To: <20080911073107.c9223a20.steve.holdoway@firetrust.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