Thread: Re: Conversion from MS Access to Postgresql
While we're on the subject of Access/Postgres, I have a consistent problem in moving tables between Access and Postgres. Access doesn't seem to want to export a fixed length character field (i.e., I have a text field that is 9 chars long but when the table is created in Postgres, it always comes out character varying(9) instead. I cannot seem to make Postgres join two tables when the type of one is char(9) and the other is character varying(9). The machine seems to go into an endless loop. A similar problem I have is with fix precision fields. I want to export a numeric(10,2) number from Access but they always end up numeric(30,6). Perhaps this is my punishment for using Access/Windows but that is not something I can change just yet. If you have any insight into why this might be happening or what I can do to fix it, I would appreciate it. BTW: Postgres 6.5.3 and Access97. len morgan -----Original Message----- From: Andrew McMillan <Andrew@catalyst.net.nz> To: G.L.Lim <limgl@grouplinks.com> Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org> Date: Monday, June 26, 2000 4:53 PM Subject: Re: [GENERAL] Conversion from MS Access to Postgresql >"G.L.Lim" wrote: >> >> Hi, >> >> I am curently using MS Access and would like to convert my existing Access >> database to Postgresql database (maybe into Postgresql dump file first if >> neccessary). Is there any utility or program that can do that? Please >> advise. > >I have a perl program which will load the .csv files you can dump from >Access. That will only move the data, of course. I generally find it >better to write a script to build the database by hand, but I think you >can export a script from Access that will work without too much editing. > >Reply if you want my perl program. > >Cheers, > Andrew. >-- >_____________________________________________________________________ > Andrew McMillan, e-mail: Andrew@cat-it.co.nz >Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington >Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 >
Len Morgan wrote:
>
> While we're on the subject of Access/Postgres, I have a consistent problem
> in moving tables between Access and Postgres. Access doesn't seem to want
> to export a fixed length character field (i.e., I have a text field that is
> 9 chars long but when the table is created in Postgres, it always comes out
> character varying(9) instead. I cannot seem to make Postgres join two
> tables when the type of one is char(9) and the other is character
> varying(9). The machine seems to go into an endless loop. A similar
> problem I have is with fix precision fields. I want to export a
> numeric(10,2) number from Access but they always end up numeric(30,6).
>
> Perhaps this is my punishment for using Access/Windows but that is not
> something I can change just yet. If you have any insight into why this
> might be happening or what I can do to fix it, I would appreciate it. BTW:
> Postgres 6.5.3 and Access97.
I don't believe that numeric precision is fully supported in 6.5.3 - I
think there is much better support in 7.0.2. Type inter-conversion also
seems to be improved in 7.0.2, but I can't comment on the specific issue
you have here :-)
I actually hand-edit my database creation scripts rather than using a
direct DDL generated from Access.
Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267
> ... or what I can do to fix it, I would appreciate it.
A specific problem report to the general list (if you want some feedback
on whether something is reasonable) or a specific problem report to
hackers or bugs (if you have a clearly defined test case) is usually all
that is required to get something fixed.
If you can't distill the problem down to a test case, then it stays an
anecdotal report that we can't do much about. But *really*, we welcome
problem reports; getting and solving these puzzles is (are?) one of the
fun things about developing for Postgres.
- Thomas
Hello Len, Once, Tuesday, June 27, 2000, 4:22:08 AM, you wrote: LM> While we're on the subject of Access/Postgres, I have a consistent problem LM> in moving tables between Access and Postgres. Access doesn't seem to want LM> to export a fixed length character field (i.e., I have a text field that is LM> 9 chars long but when the table is created in Postgres, it always comes out LM> character varying(9) instead. I cannot seem to make Postgres join two LM> tables when the type of one is char(9) and the other is character LM> varying(9). The machine seems to go into an endless loop. A similar LM> problem I have is with fix precision fields. I want to export a LM> numeric(10,2) number from Access but they always end up numeric(30,6). LM> Perhaps this is my punishment for using Access/Windows but that is not LM> something I can change just yet. If you have any insight into why this LM> might be happening or what I can do to fix it, I would appreciate it. BTW: LM> Postgres 6.5.3 and Access97. IMHO if you have a less or more complex database you can't convert it into PostgreSQL fully automatically. I did it using Pgupt, it makes sql scripts for databse schema, you can change them as you need and then create database and load data into it. -- Best regards, Yury ICQ 11831432 mailto:yura@vpcit.ru
"Len Morgan" <len-morgan@crcom.net> writes:
> ... I cannot seem to make Postgres join two
> tables when the type of one is char(9) and the other is character
> varying(9). The machine seems to go into an endless loop.
What? Specific example, please.
> A similar problem I have is with fix precision fields. I want to
> export a numeric(10,2) number from Access but they always end up
> numeric(30,6).
I don't think our 6.5.* ODBC driver knows anything about numeric,
so you're probably going to get default numeric precision if you
go through it. You might have better luck with 7.0.
regards, tom lane
How do you dump from the Postgre database? (i.e. as with MySQL, where you have mysqldump) -Morten
On Tue, 27 Jun 2000, Morten W. Petersen wrote:
> How do you dump from the Postgre database?
> (i.e. as with MySQL, where you have mysqldump)
The PostgreSQL documentation is invisible?
Karel
> The PostgreSQL documentation is invisible? Yeah, it just vanished. Not my fault. The bitbucket ate it. -Morten =)
Generally a lot of the questions here on the mailing lists are answered in the documentation. So read the docs first ;-) It's: "pg_dump databaseName > someFile.sql" "Morten W. Petersen" wrote: > > The PostgreSQL documentation is invisible? > > Yeah, it just vanished. Not my fault. The bitbucket ate it. > > -Morten =)
> Yeah, it just vanished. Not my fault. The bitbucket ate it.
Ah, so sorry. Use pg_dump.
Hope you find your docs soon ;)
- Thomas
I tried to export an Access Yes/No field to pgsql boolean and got an error message. By default, Access (and the ODBC driver) exports Yes/No to bpchar. However, I want to use bool. Any suggestions? Thanks, Mihai -----Original Message----- From: Stephen Davies <scldad@sdc.com.au> To: G.L.Lim <limgl@grouplinks.com> Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org> Date: Monday, June 26, 2000 7:54 PM Subject: Re: [GENERAL] Conversion from MS Access to Postgresql >G'day. > >Having just done it, I can confirm that converting an Access database >to PostgreSQL is very easy. > >All I did was as follows: > >1. create an empty Postgresql database, >2. cretae an ODBC DSN on the Access machine pointing at the empty >database, >3, fire up Access and Save/As/Export all tables to the ODBC target. > >Job done:-)) > >In fact, I had to do some massage to the results as the export process >does not create indexes nor sequences and I decidesd to change some of >the generated data types. > >This was also easy using the pgdump utility to dump the data and the >definition for editing. > >Be aware that any column names that include upper case letters in >Access will keep their capitalisation and require quotes in Postgresql. > >HTH, >Stephen. >"G.L.Lim" <limgl@grouplinks.com> wrote: >> Hi, >> >> I am curently using MS Access and would like to convert my existing Access >> database to Postgresql database (maybe into Postgresql dump file first if >> neccessary). Is there any utility or program that can do that? Please >> advise. >> >> Thank you. >> >> Regards, >> Geok Leng > > > > >======================================================================== >Stephen Davies Consulting scldad@sdc.com.au >Adelaide, South Australia. Voice: 08-8177 1595 >Computing & Network solutions. Fax: 08-8177 0133 > >
Thomas Lockhart wrote: > > Yeah, it just vanished. Not my fault. The bitbucket ate it. And what is a bitbucket? My dictioniary knows nothing about this word. Just for all the people who's mother tongue is not english. :-) - Herbie
Hi Mihai, Microsoft products store false as (0) and true as (-1) (Why? I don't know!). Apparently storing a (-1) requires more than bool provides. So, you can either edit all of your Access code so that it interprets (1) as true (practically, I don't recommend this), or you can migrate your yes/no fields to int2. David Boerwinkle -----Original Message----- From: Mihai Gheorghiu <tanhq@bigplanet.com> To: Stephen Davies <scldad@sdc.com.au>; G.L.Lim <limgl@grouplinks.com> Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org> Date: Tuesday, June 27, 2000 9:07 AM Subject: Re: [GENERAL] Conversion from MS Access to Postgresql >I tried to export an Access Yes/No field to pgsql boolean and got an error >message. >By default, Access (and the ODBC driver) exports Yes/No to bpchar. However, >I want to use bool. >Any suggestions? >Thanks, > >Mihai > > >-----Original Message----- >From: Stephen Davies <scldad@sdc.com.au> >To: G.L.Lim <limgl@grouplinks.com> >Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org> >Date: Monday, June 26, 2000 7:54 PM >Subject: Re: [GENERAL] Conversion from MS Access to Postgresql > > >>G'day. >> >>Having just done it, I can confirm that converting an Access database >>to PostgreSQL is very easy. >> >>All I did was as follows: >> >>1. create an empty Postgresql database, >>2. cretae an ODBC DSN on the Access machine pointing at the empty >>database, >>3, fire up Access and Save/As/Export all tables to the ODBC target. >> >>Job done:-)) >> >>In fact, I had to do some massage to the results as the export process >>does not create indexes nor sequences and I decidesd to change some of >>the generated data types. >> >>This was also easy using the pgdump utility to dump the data and the >>definition for editing. >> >>Be aware that any column names that include upper case letters in >>Access will keep their capitalisation and require quotes in Postgresql. >> >>HTH, >>Stephen. >>"G.L.Lim" <limgl@grouplinks.com> wrote: >>> Hi, >>> >>> I am curently using MS Access and would like to convert my existing >Access >>> database to Postgresql database (maybe into Postgresql dump file first if >>> neccessary). Is there any utility or program that can do that? Please >>> advise. >>> >>> Thank you. >>> >>> Regards, >>> Geok Leng >> >> >> >> >>================================================ ======================== >>Stephen Davies Consulting scldad@sdc.com.au >>Adelaide, South Australia. Voice: 08-8177 1595 >>Computing & Network solutions. Fax: 08-8177 0133 >> >> >
If you turn off the "Bool as Char" option in the driver, you get bool in Postgres. Please note that all of the work that I have done recently has been with PostgreSQL V7.0.2 and the latest ODBC driver. Things are different with older versions. Cheers, Stephen. Mihai Gheorghiu <tanhq@bigplanet.com> wrote: > I tried to export an Access Yes/No field to pgsql boolean and got an error > message. > By default, Access (and the ODBC driver) exports Yes/No to bpchar. However, > I want to use bool. > Any suggestions? > Thanks, > > Mihai > > > -----Original Message----- > From: Stephen Davies <scldad@sdc.com.au> > To: G.L.Lim <limgl@grouplinks.com> > Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org> > Date: Monday, June 26, 2000 7:54 PM > Subject: Re: [GENERAL] Conversion from MS Access to Postgresql > > > >G'day. > > > >Having just done it, I can confirm that converting an Access database > >to PostgreSQL is very easy. > > > >All I did was as follows: > > > >1. create an empty Postgresql database, > >2. cretae an ODBC DSN on the Access machine pointing at the empty > >database, > >3, fire up Access and Save/As/Export all tables to the ODBC target. > > > >Job done:-)) > > > >In fact, I had to do some massage to the results as the export process > >does not create indexes nor sequences and I decidesd to change some of > >the generated data types. > > > >This was also easy using the pgdump utility to dump the data and the > >definition for editing. > > > >Be aware that any column names that include upper case letters in > >Access will keep their capitalisation and require quotes in Postgresql. > > > >HTH, > >Stephen. > >"G.L.Lim" <limgl@grouplinks.com> wrote: > >> Hi, > >> > >> I am curently using MS Access and would like to convert my existing > Access > >> database to Postgresql database (maybe into Postgresql dump file first if > >> neccessary). Is there any utility or program that can do that? Please > >> advise. > >> > >> Thank you. > >> > >> Regards, > >> Geok Leng > > > > > > > > > >======================================================================== > >Stephen Davies Consulting scldad@sdc.com.au > >Adelaide, South Australia. Voice: 08-8177 1595 > >Computing & Network solutions. Fax: 08-8177 0133 > > > > ======================================================================== Stephen Davies Consulting scldad@sdc.com.au Adelaide, South Australia. Voice: 08-8177 1595 Computing & Network solutions. Fax: 08-8177 0133