Thread: How to perform an identical insert?

How to perform an identical insert?

From
"Axel Selk"
Date:
Dear list,
I have collected data in a Microsoft Access data base and I am trying
now to transfer this data to a postgres data base running on a Linux
computer. I generated SQL scripts for the structure as well as for the
data. I have trouble with the data inserts. I want to insert the MS
Access IDs into Postgres as all references are made to those IDs.
But I found only the ID serial format in Postgres, which does not accept
inserted IDs. Is there any way to insert the IDs from Access and then
using the serial ID for any further action in Postgres?

Please, advice.


With kind regards

Axel Selk
--

Dipl.-Ing. Axel Selk

Bremen Institute of Industrial Technology and
Applied Work Science (BIBA)
- Division BIBA PPC -
Hochschulring 20
28359 Bremen
Germany

phone:  +49 (0)421-218-5637
fax: +49 (0)421-218-5551
www.biba.uni-bremen.de



Re: How to perform an identical insert?

From
tony
Date:
On Fri, 2002-03-22 at 14:07, Axel Selk wrote:

> I have collected data in a Microsoft Access data base and I am trying
> now to transfer this data to a postgres data base running on a Linux
> computer. I generated SQL scripts for the structure as well as for the
> data. I have trouble with the data inserts. I want to insert the MS
> Access IDs into Postgres as all references are made to those IDs.
> But I found only the ID serial format in Postgres, which does not accept
> inserted IDs. Is there any way to insert the IDs from Access and then
> using the serial ID for any further action in Postgres?

In the tables put a column old_id which will hold the access ids.

I also used serial to create new ids for the postgresql base and then
did some fancy sql to get everything to coincide...

I still have the old_id column but when the client signs the "good for
service" I'll get rid of them.

Cheers

Tony

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html


Re: How to perform an identical insert?

From
Darren Ferguson
Date:
You can make the field an integer field and then have a sequence that will
be your autonumber counter such as

CREATE SEQUENCE test_seq START
<the_last_number_plus_1_in_your_MS-Access_sequence>;

CREATE TABLE foo (
  foo_id INTEGER DEFAULT NEXTVAL('test_seq') NOT NULL
);

This will then mean you do not have to insert anything into that field
since it will take the next value in the sequence.
But if you do insert a value then you can still specify your own integer
if you want. Note though that when you specify your own integer you will
have to be careful since the sequence will not know about that integer.

HTH

Darren Ferguson

On Fri, 22 Mar 2002, Axel Selk wrote:

> Dear list,
> I have collected data in a Microsoft Access data base and I am trying
> now to transfer this data to a postgres data base running on a Linux
> computer. I generated SQL scripts for the structure as well as for the
> data. I have trouble with the data inserts. I want to insert the MS
> Access IDs into Postgres as all references are made to those IDs.
> But I found only the ID serial format in Postgres, which does not accept
> inserted IDs. Is there any way to insert the IDs from Access and then
> using the serial ID for any further action in Postgres?
>
> Please, advice.
>
>
> With kind regards
>
> Axel Selk
> --
>
> Dipl.-Ing. Axel Selk
>
> Bremen Institute of Industrial Technology and
> Applied Work Science (BIBA)
> - Division BIBA PPC -
> Hochschulring 20
> 28359 Bremen
> Germany
>
> phone:  +49 (0)421-218-5637
> fax: +49 (0)421-218-5551
> www.biba.uni-bremen.de
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: How to perform an identical insert?

From
Heiko Klein
Date:
Hi Axel,

have a look at this:
http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html


The postgres serial type is nothing more than a primary key with a
default-value as nextval('..') from a sequence and a trigger.

If you make this setup manually, you can load the data before attaching
the trigger and it should work. (Though I haven't tried this.)

Heiko

Axel Selk writes:
 > Dear list,
 > I have collected data in a Microsoft Access data base and I am trying
 > now to transfer this data to a postgres data base running on a Linux
 > computer. I generated SQL scripts for the structure as well as for the
 > data. I have trouble with the data inserts. I want to insert the MS
 > Access IDs into Postgres as all references are made to those IDs.
 > But I found only the ID serial format in Postgres, which does not accept
 > inserted IDs. Is there any way to insert the IDs from Access and then
 > using the serial ID for any further action in Postgres?
 >
 > Please, advice.
 >
 >
 > With kind regards
 >
 > Axel Selk

Re: How to perform an identical insert?

From
Tom Lane
Date:
Heiko Klein <Heiko.Klein@met.no> writes:
> have a look at this:
> http://www.ca.postgresql.org/docs/aw_pgsql_book/node75.html

> The postgres serial type is nothing more than a primary key with a
> default-value as nextval('..') from a sequence and a trigger.

> If you make this setup manually, you can load the data before attaching
> the trigger and it should work. (Though I haven't tried this.)

You don't even need to do it manually; there is nothing wrong at all
with inserting into a serial column.  For example:

create table foo (key serial, other-stuff);

insert into foo values (1, ...);
insert into foo values (2, ...);
insert into foo values (3, ...);
...

As long as you don't try to insert any duplicate key values you won't
get any errors.  When you're done inserting, you need to adjust the
serial column's sequence generator to pick up where you left off,
eg with
    select setval('foo_key_seq', (select max(key) from foo));
and then you're good to go on normal operations where you don't specify
a key value during insertions.

This is essentially the same strategy that pg_dump uses to dump and
restore tables containing serial columns.

            regards, tom lane