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
CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS
-- 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!!!!';
-- 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");
EXCEPTION WHEN unique_violation THEN
-- do nothing, and loop to try the UPDATE again
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