Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012

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

    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):


    #ID serial primary key
    name text unique


    #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

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    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:
    insert into person (name) values ('Tom');
    insert into photo (person_id, size) 
    (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
    Last edited by shammat; 08-31-12 at 09:47.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts