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(at)comcast(dot)net