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

Поиск
Список
Период
Сортировка

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

От
"Markova, Nina"
Дата:
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

 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

-----Original Message-----
From: Adrian Klaver [mailto:aklaver@comcast.net]
Sent: September 9, 2008 22:43
To: pgsql-general@postgresql.org
Cc: Markova, Nina
Subject: Re: [GENERAL] FW: How to upload data to postgres

On Tuesday 09 September 2008 1:54:12 pm Markova, Nina wrote:
> So far I tried;
>
> 1)  I have copied data from Ingres in ASCII (using Ingres copydb
> command).
> 2)  created a  table in a Postgres database
> 3)  tried loading data into Potgres table - encounter problems.
>
> For 1) (the Ingres part)
> =====================
> Ingres used the following copy commands:
>
> copy site(
>         sta= varchar(0)tab,
>         ondate= 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'
>
> Normally Ingres will use this command to copy data from a file:
> copy site(
>         sta= varchar(0)tab,
>         ondate= 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)
> from '/vm04-0/home/postgres/test/site.dba'
>
> For 3)
> =====
> - I got error when I tried to copy with Ingres-like copy command.
> - Then I tried to copy with simple 'copy site from
> '/vm04-0/home/postgres/test/site-c.dba' - ERROR:  value too long for
> type character varying(5)

The ERROR explains it. The value you are bringing over from the Ingres
database is to long for a varchar(5) field.

Instead of rehashing the documentation I will point you to the relevant
section that pertains to Postgres COPY:
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

>
> - I had no luck either when used binary copying - postgres complained
> about signature:
> copy site from  '/vm04-0/home/postgres/test/site.dba'   with binary
>
> ERROR:  COPY file signature not recognized
>
> ========================
> I have couple of questions as well.
> ========================
> Q1: is there an equivalent of copydb in postgres (in Ingres copydb
> creates copy statements for all database tables in a single file)

See pg_dump:
http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html

> Q2:  how to say in postgres that a field has no default values (in
> Ingres 'not default' is used - and this produced an error in postgres
> CREATE TABLE command)

The CREATE TABLE only takes a DEFAULT clause. If you want no default
don't specify anything:

 lat float not null,

Since you specified NOT NULL you will have to specify some value on
INSERT.

>
> Create table site (
>         sta varchar(5) not null,
>         ondate varchar(8) not null,
>         offdate varchar(8) not null,
>         lat float not null not default, ----->
>         lon float not null not default
> )
>
> Q3:  How to specify storage structure of a table (again in Ingres
> 'modify' statement is used to specify btree, isam or hash structure).
> In the Postgres documentation I only saw  how to create an index with
> a specific structure.

As far as I know this cannot be done in Postgres. The only way you can
modify the storage parameters is :

"Storage Parameters

The WITH clause can specify storage parameters for tables, and for
indexes associated with a UNIQUE or PRIMARY KEY constraint. Storage
parameters for indexes are documented in CREATE INDEX. The only storage
parameter currently available for tables is:

FILLFACTOR

    The fillfactor for a table is a percentage between 10 and 100. 100
(complete packing) is the default. When a smaller fillfactor is
specified, INSERT operations pack table pages only to the indicated
percentage; the remaining space on each page is reserved for updating
rows on that page. This gives UPDATE a chance to place the updated copy
of a row on the same page as the original, which is more efficient than
placing it on a different page.
For a table whose entries are never updated, complete packing is the
best choice, but in heavily updated tables smaller fillfactors are
appropriate. "

This only applies to later versions of Postgres.

>
> In Ingres: modify site to isam unique on sta, ondate (means structure
> isam, primary key is on 2 fields - sta and ondate)
>
> Thanks in advance,
> Nina
>
> > ______________________________________________
> > From:     Markova, Nina
> > Sent:    September 9, 2008 14:32
> > To:    pgsql-general@postgresql.org
> > Subject:    How to upload data to postgres
> >
> > Hi again,
> >
> > I need to load data from Ingres database to Postgres database.
> > What's the easiest way?
> >
> > Thanks,
> > Nina



--
Adrian Klaver
aklaver@comcast.net

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

От
Adrian Klaver
Дата:
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@comcast.net

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

От
"Markova, Nina"
Дата:
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@comcast.net]
Sent: September 10, 2008 10:39
To: pgsql-general@postgresql.org
Cc: Markova, Nina; pgsql-admin@postgresql.org
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@comcast.net

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

От
Steve Holdoway
Дата:
Why not just export as 'insert into <table> (... ) values (...)' format. If ingres can't export as such, you can write
aselect statement to do this.  

Slow, sure - but the lowest common denominator.

Steve

On Wed, 10 Sep 2008 11:27:58 -0400
"Markova, Nina" <nmarkova@NRCan.gc.ca> 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@comcast.net]
> Sent: September 10, 2008 10:39
> To: pgsql-general@postgresql.org
> Cc: Markova, Nina; pgsql-admin@postgresql.org
> 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@comcast.net
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Вложения