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@xxxxxxxxxxx] Sent: September 10, 2008 10:39 To: pgsql-general@xxxxxxxxxxxxxx Cc: Markova, Nina; pgsql-admin@xxxxxxxxxxxxxx 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@xxxxxxxxxxx
