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

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

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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
    Last edited by shammat; 08-31-12 at 10:47.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

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
  •