Skip site navigation (1) Skip section navigation (2)

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

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: "Markova, Nina" <nmarkova(at)nrcan(dot)gc(dot)ca>, pgsql-admin(at)postgresql(dot)org
Subject: Re: [GENERAL] FW: How to upload data to postgres
Date: 2008-09-10 14:38:56
Message-ID: 200809100738.57156.aklaver@comcast.net (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
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(at)comcast(dot)net

In response to

Responses

pgsql-admin by date

Next:From: Markova, NinaDate: 2008-09-10 15:27:58
Subject: Re: [GENERAL] FW: How to upload data to postgres
Previous:From: Markova, NinaDate: 2008-09-10 14:14:50
Subject: Re: [GENERAL] FW: How to upload data to postgres

pgsql-general by date

Next:From: Filip RembiaƂkowskiDate: 2008-09-10 14:43:39
Subject: Re: plpgsql return select from multiple tables
Previous:From: Artis CauneDate: 2008-09-10 14:34:18
Subject: Re: plpgsql return select from multiple tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group