ON CONFLlCT DO UPDATE command cannot affect row a second time

This is a Python script that runs every night, but it stopped working. It
pulls data from a tool and moves them to Postgres databases.

In the Python shell I get the following error/hint:

psycopg2.ProgrammingError: 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.

Here is the code part:

def update_tbl(job):
""" Try to insert new record into the table. If that
record already exists, then update that row. This is based
off primary keys marked in the settings file."""

to_tbl = job.get('to_table')
from_tbl = get_temp_name(job['to_table'])
col_map = job.get('col_map')
cols = ', '.join([x[1] for x in col_map])
sel_cols = ', '.join(map(
lambda x: x[1] if len(x) <= 4 else x[4].replace('__alias__', x[1]),
col_map))
uids = ', '.join([x[1] for x in col_map if x[3]])
exclusions = list(filterfalse(lambda x: x[3], col_map))
live_excs = ', '.join([x[1] for x in exclusions])
temp_excs = ', '.join(["Excluded.%s" %(x[1]) for x in exclusions])
clause_up = build_exc_clause(col_map, 'live', 'Excluded')
conn = get_conn(job.get('to_db'))

with conn.cursor() as cur:
upsert = """
INSERT INTO {t} as live ({cols}) (
SELECT {sel_cols} FROM {f}
)
""".format(t=to_tbl, f=from_tbl, cols=cols, sel_cols=sel_cols)

# If there are no fields other than primary keys
# (such as document_type_map),
# just do the insert
if job.get("noupdateondupe", False):
upsert += """
ON CONFLICT ({uids})
DO NOTHING
""".format(uids=uids)
elif len(uids) > 0 and len(live_excs) > 0:
upsert += """
ON CONFLICT ({uids})
DO UPDATE SET ({lexc}) = ({exc})
WHERE ({c})
""".format(uids=uids, lexc=live_excs, exc = temp_excs, c=clause_up)

logging.info("about to update %s"%(to_tbl))
logging.info(upsert)

try:
# logging.info("upsert query")
# logging.info(upsert)
cur.execute(upsert)
conn.commit()
except IntegrityError:
conn.rollback()
if job.get('add_sequence', False):
add_sequence(to_tbl, job)
cur.execute(upsert)
conn.commit()
logging.info(upsert)
else:
raise
conn.close()
I found this in a "utils" file which could be helpful since it has "upsert"
in it:

# If there are no fields other than primary keys
# (such as document_type_map),
# just do the insert
if job.get("noupdateondupe", False):
upsert += """
ON CONFLICT ({uids})
DO NOTHING
""".format(uids=uids)
elif len(uids) > 0 and len(live_excs) > 0:
upsert += """
ON CONFLICT ({uids})
DO UPDATE SET ({lexc}) = ({exc})
WHERE ({c})
""".format(uids=uids, lexc=live_excs, exc = temp_excs, c=clause_up)

logging.info("about to update %s"%(to_tbl))
logging.info(upsert)

try:
# logging.info("upsert query")
# logging.info(upsert)
cur.execute(upsert)
conn.commit()
except IntegrityError:
conn.rollback()
if job.get('add_sequence', False):
add_sequence(to_tbl, job)
cur.execute(upsert)
conn.commit()
logging.info(upsert)
else:
raise
conn.close()
So in the log file, the script seems to work until here and the last bit of
text shows:

2020-04-30 10:33:18,164 about to update my_data
2020-04-30 10:33:18,164
INSERT INTO my_data as live (partid, id, refnum, originalnum,
catalognum, catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id) (
SELECT partid, id, refnum, originalnum, catalognum,
catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id FROM temp_my_data
)

ON CONFLICT (id)
DO UPDATE SET (partid, refnum, originalnum, catalognum,
catalogpnwp, originalMFG, manufacturerid, originaldescription,
originalrevision, contentid, status_id, createddate, comment, na_id,
na_date, site_id) = (Excluded.partid, Excluded.refnum, Excluded.originalnum,
Excluded.catalognum, Excluded.catalogpnwp, Excluded.originalMFG,
Excluded.manufacturerid, Excluded.originaldescription,
Excluded.originalrevision, Excluded.contentid, Excluded.status_id,
Excluded.createddate, Excluded.comment, Excluded.na_id, Excluded.na_date,
Excluded.site_id)
WHERE (live.id = Excluded.id)
and the script stops working.

I'm not a database guy and certainly not a Python expert. How do I fix this
?

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-novice-f2132464.html