Thread: Re: [GENERAL] FW: How to upload data to postgres
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
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