pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Converting Access tables to PostGre 2+ messages / 2 participants [nested] [flat]
* Converting Access tables to PostGre @ 2000-12-05 14:40 Richard Holland <rholland@equitechinfo.com> 0 siblings, 0 replies; 2+ messages in thread From: Richard Holland @ 2000-12-05 14:40 UTC (permalink / raw) To: pgsql-interfaces I've come across tons of information on using Access with PostGre, and am thankful for this. Be this as it may, the upsizing tool I found to convert our access databases to postgre seems to puke on our 800+ meg databases (which is why we're doing this, our software was created by idiots with no scalability in mind). I downloaded pgadmin, the latest version as of a week ago. This works great, congrats to it's maintainers. My problem is the same above mentioned idiots used double and single field types to handle money. Being that our software handles property tax for government counties, we can't have any rounding errors. So I figured on using the numeric(x,y) type, so I fire up the migration tool built into pgadmin, edit the typemaps to throw singles and doubles to numeric, and it fails because it doesnt seem to give the numerics a length. It feeds postgre fieldname numeric(0,lenghtofdecimalsspecifiedinaccess) this fails.. anybody have a better solution for migrating our data? I can make all 300 tables by hand if need be, but it's getting the millions of records in i'm worried about. Thanks in advance, Richard A. Holland CCNP,MCSE,CCDA Equitech Information Systems 417.832.0366 ^ permalink raw reply [nested|flat] 2+ messages in thread
* RE: Converting Access tables to PostGre @ 2000-12-05 15:07 Dave Page <dpage@vale-housing.co.uk> 0 siblings, 0 replies; 2+ messages in thread From: Dave Page @ 2000-12-05 15:07 UTC (permalink / raw) To: 'Richard Holland' <rholland@equitechinfo.com>; pgsql-interfaces > -----Original Message----- > From: Richard Holland [mailto:rholland@equitechinfo.com] > Sent: 05 December 2000 14:41 > To: pgsql-interfaces@postgresql.org > Subject: [INTERFACES] Converting Access tables to PostGre > > > I've come across tons of information on using Access with > PostGre, and am thankful for this. Be this as it may, the > upsizing tool I > found to convert our access databases to postgre seems to > puke on our 800+ meg databases (which is why we're doing this, our > software was created by idiots with no scalability in mind). > I downloaded pgadmin, the latest version as of a week ago. This works > great, congrats to it's maintainers. Thanks. > My problem is the same > above mentioned idiots used double and single field types to handle > money. Being that our software handles property tax for > government counties, we can't have any rounding errors. So I > figured on > using the numeric(x,y) type, so I fire up the migration tool > built into pgadmin, edit the typemaps to throw singles and doubles to > numeric, and it fails because it doesnt seem to give the > numerics a length. It feeds postgre fieldname > numeric(0,lenghtofdecimalsspecifiedinaccess) pgAdmin attempts to figure out the scale/precision of the numeric from the source database using the NumericScale and Precision properties of the ADOX.Field object representing the source field (If I'm using the wrong properties here corrections would be appreciated...). As the source database has floats/longs in it (and therefore no (x,y) values) you will get odd results (eg. zeros). > this fails.. anybody have a better solution for migrating > our data? I can make all 300 tables by hand if need be, but > it's getting > the millions of records in i'm worried about. I suspect that pgAdmin would fail with a Migration this large as well as it creates a recordset from the source table and uses that to fire insert queries at PostgreSQL - in my experience ADO recordsets get unusable around the 100,000 record mark. I would suggest a different tactic along the lines of: 1) Migrate the table/index definitions 'as-is' using pgAdmin's migration wizard. 2) Dump the PostgreSQL database using pg_dump and use vi or something to change the floats to numerics. 3) Reload the database. 4) Write some Access/VB code to dump the source databases to ASCII text files. Use a quote and a delimiter eg: 'fld1'|'fld2'|'fld3'| Hint: If you have ' in you data replace it with '' and don't use ' as a quote character 5) Use the pgAdmin Import Wizard to load the ASCII files. I'd be interested to know how pgAdmin copes with such large text files if you try this method... HTH, Regards, Dave. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-12-05 15:07 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-12-05 14:40 Converting Access tables to PostGre Richard Holland <rholland@equitechinfo.com> 2000-12-05 15:07 RE: Converting Access tables to PostGre Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox