Re: [GENERAL] FW: How to upload data to postgres

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> 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@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
> 
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Attachment: pgphPSH8SeVZT.pgp
Description: PGP signature


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux