Thread: Re: Conversion from MS Access to Postgresql

Re: Conversion from MS Access to Postgresql

From
"Len Morgan"
Date:
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
>


Re: Conversion from MS Access to Postgresql

From
Andrew McMillan
Date:
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

Re: Conversion from MS Access to Postgresql

From
Thomas Lockhart
Date:
> ... 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

Re[2]: Conversion from MS Access to Postgresql

From
Yury Don
Date:
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



Re: Conversion from MS Access to Postgresql

From
Tom Lane
Date:
"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 to dump from Postgre

From
"Morten W. Petersen"
Date:
How do you dump from the Postgre database?
(i.e. as with MySQL, where you have mysqldump)

-Morten


Re: How to dump from Postgre

From
Karel Zak
Date:
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


Re: How to dump from Postgre

From
"Morten W. Petersen"
Date:
>  The PostgreSQL documentation is invisible?

Yeah, it just vanished. Not my fault. The bitbucket ate it.

-Morten =)


Re: How to dump from Postgre

From
"Poul L. Christiansen"
Date:
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 =)


Re: How to dump from Postgre

From
Thomas Lockhart
Date:
> Yeah, it just vanished. Not my fault. The bitbucket ate it.

Ah, so sorry. Use pg_dump.

Hope you find your docs soon ;)

                     - Thomas

Re: Conversion from MS Access to Postgresql

From
Mihai Gheorghiu
Date:
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
>
>


Re: How to dump from Postgre

From
Herbert Liechti
Date:
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



Re: Conversion from MS Access to Postgresql

From
Date:
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
>>
>>
>


Re: Conversion from MS Access to Postgresql

From
Stephen Davies
Date:
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