Обсуждение: How to perform an identical insert?
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
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
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
>
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
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