-2

Trying to insert data to a new empty table with unique constraints. When I remove the ON CONFLICT statement the code errors out immediately with a violation of the unique constraint in the first insert try even though the table is empty. When I remove the unique constraints from the table, the data is duplicated when I rerun the code.

I would like the insert to verify if the row exists and do nothing and if it does not exist, to make a new entry. I'm running postgres 11.3 on Mac OSX. Any help appreciated.

psycopg2.extras.execute_values(cur,
    "insert  into ferc_hots (html,link) values %s",
    values)
on conflict (html,link) do nothing

con.commit()  
print("Records inserted successfully")  
con.close()
wiedhas
  • 57
  • 7
  • 2
    `ON CONFLICT` should be within the SQL portion of the code, not part of the Python code. – Nick May 31 '19 at 15:27
  • not sure how to fix this. Still getting a syntax error when I move the ) after values to the end of the conflict statement – wiedhas May 31 '19 at 15:55

1 Answers1

0

Your fix should really be simple since you just have the on conflict line in the wrong place.

psycopg2.extras.execute_values(cur,
    """insert  into ferc_hots (html,link) values %s
       on conflict (html,link) do nothing""", # Moved to inside SQL
    values)

con.commit()  
print("Records inserted successfully")  
con.close()
Nick
  • 7,103
  • 2
  • 21
  • 43
  • Thanks for the reply. That code produces the following error: psycopg2.errors.InvalidColumnReference: "there is no unique or exclusion constraint matching the ON CONFLICT specification". Both columns html and link have unique constraints as well. – wiedhas Jun 01 '19 at 00:57
  • Is there a single unique constraint on BOTH - not each - columns? – Nick Jun 01 '19 at 01:08
  • there is a unique constraint on each column, not one single constraint for both: Indexes: "ferc_hots_html_key" UNIQUE CONSTRAINT, btree (html) "ferc_hots_link_key" UNIQUE CONSTRAINT, btree (link) – wiedhas Jun 01 '19 at 15:04
  • That is you problem. In your ON CONFLICT clause you are specifying a single constraint (html, link) as having the conflict - not both individually. – Nick Jun 01 '19 at 15:07
  • Would creating a unique index work better than just creating unique constraints for both tables? https://stackoverflow.com/questions/23542794/postgres-unique-constraint-vs-index . – wiedhas Jun 01 '19 at 16:51
  • OK so i fixed the ON CONFLICT issue. I had 3 items in the "values" list and did not have these items in the insert code. I also defined the primary key value to include both columns (html,link) in the postgres table. All working now. Thanks! – wiedhas Jun 01 '19 at 23:11
  • How to do this in Odoo? – Jessie M Feb 01 '23 at 04:24