Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010

    Unanswered: Creating a trigger that Performs Upserts

    Dear Sirs et Madames,
    I am new-ish to postgres and was trying to create a mechanism which when one table ("MEMBER_TEST" table) is inserted with data (via COPY command from CSV file) would allow a secondary table ("rufiji_individual") to be inserted with the data (if it already does not have it) or updated with data, if it already has that particular row.

    I borrowed from PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Control Structures. which explains how to create an upsert function so I modified if to create an upsert TRIGGER:

            -- first try to update the key
            UPDATE rufiji_individual SET "fullName" = NEW."NAME","sex" = NEW."SEX",dob = NEW."BIRTH_DATE","insertDate"= NEW."ENTRY_DATE",
    		"dataStatus"= NEW."STATUS_DAT","relationToHead"= NEW."RLTN_HEAD", occupation=NEW."KAZI","occupationOther" = NEW."NYINGINE",education=NEW."EDUCATION",
    		"entryType" = NEW."ENTRY_TYPE", "entryDate" = NEW."ENTRY_DATE","exitType" = NEW."EXIT_TYPE","exitDate"= NEW."EXIT_DATE","maritalStatus"= NEW."NDOA",
    		"maritalStatusOther" = NEW."NDOA_ING" WHERE  "externalID" = NEW."PERM_ID";
            IF found THEN
            raise notice 'found!!!!';
                RETURN NEW;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
                INSERT INTO rufiji_individual(uuid,"fullName",sex) VALUES ((select uuid_generate_v1mc()),NEW."NAME",NEW."SEX");
                RETURN NEW;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing, and loop to try the UPDATE again
        END LOOP;
    LANGUAGE plpgsql;
    This partly seems to work, i.e. rufiji_individual gets inserted with data when the copy command is run on MEMBER_TEST. However when the CSV file is edited and the copy command is run again, rather than rufiji_individual getting updated, new (mostly duplicate) data is entered rather than an update occurring.

    Could anyone advise me if am going about this the right way, and if not, how I could achieve what am attempting to achieve?

    Thanks in advance

  2. #2
    Join Date
    May 2008
    I assume this is a BEFORE trigger? You need to return NULL -- not NEW -- to cancel the original operation, which is what you want to do if you're substituting an UPDATE for an INSERT (or vice versa).

    Also, you should only need to override the behavior of either UPDATE or INSERT, not both. So for a BEFORE INSERT, your logic will look something like:
    Try an UPDATE.
    If successful, return NULL (cancel the INSERT).
    Otherwise return NEW (continue the INSERT).

Posting Permissions

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