ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

FOR rec IN SELECT inv_no, transactiontime, LEAD(transactiontime) OVER (PARTITION BY identification_number) AS leadtime, LAG(transactiontime) OVER (PARTITION BY identification_number) AS lagtime,
transactiondate, trans_type, reasoncode, LEAD(reasoncode) OVER (PARTITION BY identification_number) AS leadcode, LAG(reasoncode) OVER (PARTITION BY identification_number) AS lagcode,
identification_number, last_name, first_name, middle_name, suffix, house_number, housenumbersuffix, street_name, streettypecodename, direction, post_direction, apt_num,
city, state, zip, address_line_1, address_line_2, address_line_3, locality_code, localityname, precinct_code_value, precinctname, townname_code_value, townname_code_name,
townprec_code_value, townprec_code_name, cong_code_value, stsenate_code_value, sthouse_code_value, gender, dob, registration_date, effective_date, mailing_address_line_1,
mailing_address_line_2, mailing_address_line_3, mailing_city, mailing_state, mailing_zip, superdist_code_value, superdist_code_name, mailing_address_use_residence, status,
protected
FROM current_working_trans LOOP
    code := rec.reasoncode;
CASE
    WHEN ((rec.reasoncode = 'TRANSFER IN' AND rec.leadcode = 'TRANSFER OUT' AND (rec.leadtime::time < (rec.transactiontime::time + interval '1 minute')))
    OR (rec.reasoncode = 'CHANGE IN' AND rec.leadcode = 'CHANGE OUT' AND (rec.leadtime::time < (rec.transactiontime::time + interval '1 minute')))) THEN
            UPDATE test_va_new_voter
                SET (sourceid, lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype,
                 regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips,
                 jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear,
                 birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5, regstate) =
            (rec.identification_number, rec.last_name, rec.first_name, rec.middle_name, rec.suffix, rec.house_number, rec.housenumbersuffix,
            rec.street_name, rec.streettypecodename, rec.post_direction, rec.apt_num, rec.city, rec.state, rec.zip::int, rec.address_line_1, rec.address_line_2,
            rec.locality_code::int, rec.localityname, rec.precinct_code_value, rec.precinctname, rec.cong_code_value::int, rec.stsenate_code_value::int,
            rec.sthouse_code_value::int, rec.gender, EXTRACT(year FROM rec.dob), EXTRACT(month FROM dob), EXTRACT(day FROM rec.dob),
            rec.registration_date, rec.mailing_address_line_1, rec.mailing_address_line_2, rec.mailing_city, rec.mailing_state, LEFT(rec.mailing_zip, 5), 'VA')
            FROM current_working_trans WHERE rec.identification_number = sourceid;

            DELETE FROM current_working_trans WHERE rec.transactiontime = transactiontime AND nvrareasoncode = rec.reasoncode;
            DELETE FROM current_working_trans WHERE rec.leadtime = transactiontime AND rec.leadcode = reasoncode;

    WHEN code IN('NEW','REACTIVATE','REINSTATE', 'TRANSFER IN', 'CHANGE IN') THEN
                INSERT INTO test_voter
                (sourceid, lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype,
                 regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips,
                 jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear,
                 birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5, regstate)
                SELECT rec.identification_number, rec.last_name, rec.first_name, rec.middle_name, rec.suffix, rec.house_number, rec.housenumbersuffix,
                rec.street_name, rec.streettypecodename, rec.post_direction, rec.apt_num, rec.city, rec.state, rec.zip::int, rec.address_line_1, rec.address_line_2,
                rec.locality_code::int, rec.localityname, rec.precinct_code_value, rec.precinctname, rec.cong_code_value::int, rec.stsenate_code_value::int,
                rec.sthouse_code_value::int, rec.gender, EXTRACT(year FROM rec.dob), EXTRACT(month FROM dob), EXTRACT(day FROM rec.dob),
                rec.registration_date, rec.mailing_address_line_1, rec.mailing_address_line_2, rec.mailing_city, rec.mailing_state, LEFT(rec.mailing_zip, 5), 'VA'
                FROM current_working_trans
                ON CONFLICT (sourceid)
                DO UPDATE
                SET (lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype,
                 regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips,
                 jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear,
                 birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5, regstate) =
                (SELECT rec.last_name, rec.first_name, rec.middle_name, rec.suffix, rec.house_number, rec.housenumbersuffix,
                rec.street_name, rec.streettypecodename, rec.post_direction, rec.apt_num, rec.city, rec.state, rec.zip::int, rec.address_line_1, rec.address_line_2,
                rec.locality_code::int, rec.localityname, rec.precinct_code_value, rec.precinctname, rec.cong_code_value::int, rec.stsenate_code_value::int,
                rec.sthouse_code_value::int, rec.gender, EXTRACT(year FROM rec.dob), EXTRACT(month FROM rec.dob), EXTRACT(day FROM rec.dob),
                rec.registration_date, rec.mailing_address_line_1, rec.mailing_address_line_2, rec.mailing_city, rec.mailing_state, LEFT(rec.mailing_zip, 5), 'VA');
            DELETE FROM current_working_trans WHERE reasoncode = rec.nvrareasoncode AND transactiontime = rec.transactiontime;
    END CASE;
END LOOP;

FOR rec IN SELECT *
FROM current_working_trans LOOP
    code := rec.reasoncode;
CASE
    WHEN code IN('NEW','REACTIVATE','REINSTATE', 'TRANSFER IN', 'CHANGE IN') THEN
                INSERT INTO test_voter
                (sourceid, lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype,
                 regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips,
                 jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear,
                 birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5, regstate)
                SELECT rec.identification_number, rec.last_name, rec.first_name, rec.middle_name, rec.suffix, rec.house_number, rec.housenumbersuffix,
                rec.street_name, rec.streettypecodename, rec.post_direction, rec.apt_num, rec.city, rec.state, rec.zip::int, rec.address_line_1, rec.address_line_2,
                rec.locality_code::int, rec.localityname, rec.precinct_code_value, rec.precinctname, rec.cong_code_value::int, rec.stsenate_code_value::int,
                rec.sthouse_code_value::int, rec.gender, EXTRACT(year FROM rec.dob), EXTRACT(month FROM dob), EXTRACT(day FROM rec.dob),
                rec.registration_date, rec.mailing_address_line_1, rec.mailing_address_line_2, rec.mailing_city, rec.mailing_state, LEFT(rec.mailing_zip, 5), 'VA'
                FROM current_working_trans
                ON CONFLICT (sourceid)
                DO UPDATE
                SET (lastname, firstname, middlename, namesuffix, reghousenum, reghousesfx, regstname, regsttype,
                 regstpost, regunitnumber, regcity, regsta, regzip5, registrationaddr1, registrationaddr2, county_fips,
                 jurisname, precinct, precinctname, cd_nextelection, sd_next_election, ld_nextelection, sex, birthyear,
                 birthmonth, birthday, registrationdate, mailingaddr1, mailingaddr2, mailcity, mailsta, mailzip5, regstate) =
                (SELECT rec.last_name, rec.first_name, rec.middle_name, rec.suffix, rec.house_number, rec.housenumbersuffix,
                rec.street_name, rec.streettypecodename, rec.post_direction, rec.apt_num, rec.city, rec.state, rec.zip::int, rec.address_line_1, rec.address_line_2,
                rec.locality_code::int, rec.localityname, rec.precinct_code_value, rec.precinctname, rec.cong_code_value::int, rec.stsenate_code_value::int,
                rec.sthouse_code_value::int, rec.gender, EXTRACT(year FROM rec.dob), EXTRACT(month FROM rec.dob), EXTRACT(day FROM rec.dob),
                rec.registration_date, rec.mailing_address_line_1, rec.mailing_address_line_2, rec.mailing_city, rec.mailing_state, LEFT(rec.mailing_zip, 5), 'VA');
            DELETE FROM current_working_trans WHERE reasoncode = rec.nvrareasoncode AND transactiontime = rec.transactiontime;
    END CASE;
END LOOP;