'Cascade' Insert - how to speed up insert with reference key

[alex]

Hi Experts,

Let's assume we ve got tables like that (I've got much complicated structure but I just want to show you my problem by example):

Person:

#ID serial primary key
name text unique

Photo:

#Photo_Id serial primary key
person_id integer references Person (ID)
size integer

Now, I want to do the following steps:

Add 1 Person and for this record add 1 Photo. I have implemented it in this way so far:

INSERT INTO Person (name) VALUES ('Tom');
SELECT ID FROM Person WHERE name = 'Tom'; # I save it to the variable f. e. X
INSERT INTO Photo (person_id, size) VALUES (X, 1234);

It works really slow because I do it in my code millions times and SELECT query is expensive.

Could you tell me how I can increase the speed of that

I implemented code in perl using DBI if you need this information

Thank you in advance
Regards,
Alex

[marian]

A serial column is based on a sequence.

In the second INSERT you can use the currval function to retrieve the value of the generated sequence value:
Code:

insert into person (name) values ('Tom');
insert into photo (person_id, size)
values
(currval('person_id_seq'), 1234);

As an alternative you can also use lastval() instead of currval('person_id_seq')

For more details see the manual: PostgreSQL: Documentation: 9.1: Sequence Manipulation Functions