Обсуждение: Re: [GENERAL] FW: How to upload data to postgres

----------- Original message ----------------------
From: "Markova, Nina" <nmarkova@NRCan.gc.ca>
>
> 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
>

I don't know how to do that. Back to your original problem, below is your copy statement from Ingres:

copy site(
        sta= varchar(0)tab,
        >
        offdate= varchar(0)tab,
        lat= c0tab,
        lon= c0tab,
        elev= c0tab,
        regist_code= varchar(0)tab,
        vault_cond= varchar(0)tab,
        geology= varchar(0)tab,
        comment= varchar(0)tab,
        initials= varchar(0)tab,
        lddate= c0nl,
        nl= d0nl)
into '/tmp/site.dba'

According to the information below you should be able to change the varchar datatypes to char(0)tab and eliminate the
lengthspecifier. Seems this can be done for all data types and will produce a file with string representations of the
data.The  downside is the strings are padded to width of the column. 

http://docs.ingres.com/sqlref/ColumnFormats#o1232

 ---
> --
> Adrian Klaver
> aklaver@comcast.net

Adrian,

The trick seems to work. Thanks!

Nina
-----Original Message-----
From: Adrian Klaver [mailto:aklaver@comcast.net]
Sent: September 10, 2008 11:58
To: Markova, Nina
Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org
Subject: RE: [GENERAL] FW: How to upload data to postgres


----------- Original message ----------------------
From: "Markova, Nina" <nmarkova@NRCan.gc.ca>
>
> 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
>

I don't know how to do that. Back to your original problem, below is
your copy statement from Ingres:

copy site(
        sta= varchar(0)tab,
        >
        offdate= varchar(0)tab,
        lat= c0tab,
        lon= c0tab,
        elev= c0tab,
        regist_code= varchar(0)tab,
        vault_cond= varchar(0)tab,
        geology= varchar(0)tab,
        comment= varchar(0)tab,
        initials= varchar(0)tab,
        lddate= c0nl,
        nl= d0nl)
into '/tmp/site.dba'

According to the information below you should be able to change the
varchar datatypes to char(0)tab and eliminate the length specifier.
Seems this can be done for all data types and will produce a file with
string representations of the data. The  downside is the strings are
padded to width of the column.

http://docs.ingres.com/sqlref/ColumnFormats#o1232

 ---
> --
> Adrian Klaver
> aklaver@comcast.net

From: "Markova, Nina" <nmarkova@NRCan.gc.ca>
> 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.

You can possibly use perl's XML::Xpath for XML import (DBIx::XML_RDB for
export), assuming the table is already created in postgresql.

You just need to find the right node names.


HTH

Ben