Re: Access 97/Postgres migration

On Wed, Apr 11, 2001 at 03:18:38PM +0000, ZHU Jia wrote:
> Hi there,
>
> we are considering using Postgres as our new backend DB. But we have a
> rather complicated Access application which we need to migrate. The
> idea is to export all the tables from Access to Postgres, then link
> them back using ODBC so that the Access interface will remain
> untouched. I just wonder how it would work with the auto_increment
> data type of Access, I've read that Postgres has the data type
> "Serial" but it doesn't seem that I can insert a value into it because
> it should be generated automatically. Now the problem is how can I
> convert the existing IDs (primary key) to serial? And would this
> setup work well at all? Is there anything I should keep in mind from
> the beginning? Any hints or tips would be highly appreciated, and
> many thanks in advance!

Haven't any experience with the ODBC driver via Access, but you can
insert into the serial column provided your value is unique. However,
it may screw up subsequent inserts if you don't update the sequence.

So, instead define your integer key fields as "int4" types. Then bulk
load your data. Then for each table that should be auto numbered,
create a sequence with the start = max("ID") + 1 of the respective
table. Then use "alter table" to set the default to
nextval('mytable_id_seq').

Like:

create table foo (
id int4 PRIMARY KEY,
...
);

copy foo from '/path/to/file' using delimiters '|';
-- or use a script from Access to do inserts...

select max(id) + 1 from foo;

create sequence foo_id_seq start [value from above];

alter table foo alter column foo set default nextval('foo_id_seq');

Rinse, Repeat. ;)

--
Eric G. Miller <egm2(at)jps(dot)net>