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

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 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,

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

---

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@NRCan.gc.ca> wrote:

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