Use the `INSERT ... ON CONFLICT` with the `WHERE` condition as you showed it.

Then check how many rows were affected, and if there was none, throw an error. You can do that either in client code or in a database function.

With psycopg2, you can get the number of rows modified from the [`rowcount`][1] property of the cursor, as shown in [this answer][2].


  [1]: https://www.psycopg.org/docs/cursor.html#cursor.rowcount
  [2]: https://stackoverflow.com/a/24441715/6464308