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

PostgreSQL database administration questions.

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

Postby alex » Tue Sep 11, 2012 1:30 am

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
alex
 
Posts: 6
Joined: Tue Jul 14, 2009 9:43 am

Invitations sent: 0
Successful invitations: 0

Re: 'Cascade' Insert - how to speed up insert with reference

Postby marian » Tue Sep 11, 2012 1:32 am

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
marian
Site Admin
 
Posts: 16
Joined: Tue Jul 14, 2009 9:13 am

Invitations sent: 15
Successful invitations: 2


Return to PostgreSQL Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron