Обсуждение: FW: How to upload data to postgres
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)
- 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)
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)
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.
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
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
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
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
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
----------- 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