title: Upsert in Scrapy + PostgreSQL Pipeline?
body: <p>I have a Scrapy spider that inserts the scraped data into a PostgreSQL database.
  It works fine on the first run, but if the primary key already exists, I get the corresponding errors</p>
  <p>I tried to implement the upsert DO NOTHING or DO UPDATE commands I read about. I even read the Blog from Depesz about why upsert is so complicated - and while it was interesting, it was a bit &quot;high&quot; for me on some parts and I won't get any race problems with this project.</p>
  <p>I tried to use this code for my pipline:</p>
  <pre><code> def store_db(self, item):
      if &quot;pos&quot; in item:
      
          self.cur.execute(&quot;insert into horses(pos, draw, dwinner, dnext, startnumber, horsename, horsecountry, odds, jockey, trainer, weight, age, headgear, ofr, rp, ts, rprc, date, track, racename) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (pos, draw, dwinner, dnext, startnumber, horsename, horsecountry, odds, jockey, trainer, weight, age, headgear, ofr, rp, ts, rprc, date, track, racename) DO NOTHING&quot;,
              (
              item['pos'],
              item['draw'],
              item['dwinner'],
              item['dnext'],
              item['startnumber'],
              item['pferde'],
              item['horsecountry'],
              item['odd'],
              item['jockey'],
              item['trainer'],
              item['weightkg'],
              item['alter'],
              item['headgear'],
              item['officalrating'],
              item['rp'],
              item['ts'],
              item['rprc'],
              item['date'],
              item['track'],
              item['racename']
              )) 
          self.connection.commit()
          return item
  </code></pre>
  <p>But I guess I messed it up.
  How do I implement the ON CONFLICT command in this pipeline correctly?</p>
  <p>****** EDIT *****
  In another table, I set date, track and racename as a primary key. If I update the code to only these three</p>
  <blockquote>
  <p>(sel.cur.execute(&quot;insert into( date, track, racename) value(%s, %s,
  %s) ON CONFLICT (date, track, racename) DO NOTHING</p>
  </blockquote>
  <p>it works as intended.
  But - if I add another, not primary key field like &quot;racetype&quot;, I get this error:</p>
  <pre><code>      File &quot;C:\Anaconda3\envs\virtual_workspace\lib\site-packages\twisted\internet\defer.py&quot;, line 858, in _runCallbacks
      current.result = callback(  # type: ignore[misc]
    File &quot;C:\Anaconda3\envs\virtual_workspace\lib\site-packages\scrapy\utils\defer.py&quot;, line 150, in f
      return deferred_from_coro(coro_f(*coro_args, **coro_kwargs))
    File &quot;C:\Users\Schalumpa\projects\jsontest\jsontest\pipelines.py&quot;, line 30, in process_item
      self.store_db(item)
    File &quot;C:\Users\Schalumpa\projects\jsontest\jsontest\pipelines.py&quot;, line 42, in store_db
      item['racetype']
  KeyError: 'racetype'
  </code></pre>
  <p>I read <a href="https://stackoverflow.com/questions/36799104/how-to-correctly-do-upsert-in-postgres-9-5">here</a>: that <em>&quot;The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error&quot;</em> - does that mean that I can only use &quot;ON CONFLICT&quot; on Primary keys - and that the rest still gets processed?
  I thought on &quot;DO NOTHING&quot; the operation on this column will just be aborted, I don`t want to update anything in the tables, as there is no &quot;new&quot; data once it already is insertet once.</p>