Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005

    Unanswered: Insert or reject

    Newbie with Postgres, and want to see if I'm doing this correctly before I write a bunch of useless Functions. I have a spreadsheet that I will be getting once a year, with approx. 50,000 rows on it. I need to import it into a database and there will be a lot of duplicate records for many of the tables. Since it only happens once a year, I don't care how long it takes to import, but I want to see if I'm doing this efficiently.

    Here's an example:
    TABLE: x(x_rowid, x_code, x_name) // where my x_rowid is my IPK
    INIQUE INDEX: ON x(x_code, x_name)

    Data: ('FOO', 'BAR')

    So during the import, the 2nd occurrence of this data stopped the transaction because of the error (I think that's what happened)

    So I wrote a pl/sql Function

    SELECT INTO my_rowid x_rowid FROM x WHERE x_code = 'FOO' AND x_name = 'BAR';
    IF found = FALSE then
    INSERT statement...;
    END IF;

    So my question is, is this the right thing to be doing? It looks like it works, but maybe there's a better way.


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    No need for a function. You can do that with a single statement:

    insert into x  (x_rowid, x_code, x_xname, ...)
    select 42, 'FOO', 'BAR', ...
    where not exists (select 1 from x
                       where x_code = 'FOO'
                       and x_name 'BAR')
    Using a writeable CTE you can also turn this into an update/insert (aka "merge") solution.
    For an example see here: sql - Insert, on duplicate update (postgresql) - Stack Overflow

    Another alternative is to guard each insert with a savepoint, catch the exception in case of an duplicate and then simply rollback the savepoint, keeping the overall transaction intact.

    There are also tools that can do that kind of import automatically without the need for you to write any code.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

  3. #3
    Join Date
    Jun 2005
    thank you, that works for what I need :-)

Posting Permissions

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