Nested transactions

On Mon, 26 Jun 2000, Andrea Aime wrote:

does postgres support nested transactions?

No.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

pgupt: http://ds.dial.pipex.com/boylesa/pgupt/pgupt.shtml

"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.

Thank you.

Regards,
Geok Leng

Unfortunately, Postgres doesn't support nested transactions. At least I'm
99% sure it doesn't.

At 10:53 AM 6/26/00, Andrea Aime wrote:

Hey,
I believe pgAdmin does that. Even if you don't use this part of it, you
should still use it for general management of your postgresql databases. It
rocks.

http://www.vale-it.demon.co.uk/freeware/pgadmin/

Ole Gjerde

----- Original Message -----
From: "G.L.Lim" <limgl@grouplinks.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, June 26, 2000 10:59 AM
Subject: [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

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

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

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

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

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

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