title: Upsert error (On Conflict Do Update) pointing to duplicate constrained values
body: <p>I have a problem with <code>ON CONFLICT DO UPDATE</code> in Postgres 9.5 when I try to use more than one source in the FROM statement.</p>
  
  <p>Example of <strong>working</strong> code:</p>
  
  <pre><code>    INSERT INTO new.bookmonographs  (citavi_id, abstract, createdon, edition, title, year)
  SELECT "ID", "Abstract", "CreatedOn"::timestamp, "Edition", "Title", "Year"
  FROM old."Reference"
  WHERE old."Reference"."ReferenceType" = 'Book'
      AND old."Reference"."Year" IS NOT NULL
      AND old."Reference"."Title" IS NOT NULL
  ON CONFLICT (citavi_id) DO UPDATE 
      SET (abstract, createdon, edition, title, year) = (excluded.abstract, excluded.createdon, excluded.edition, excluded.title, excluded.year)
  ; 
  </code></pre>
  
  <p><strong>Faulty</strong> code:</p>
  
  <pre><code>    INSERT INTO new.bookmonographs  (citavi_id, abstract, createdon, edition, title, year)
  SELECT "ID", "Abstract", "CreatedOn"::timestamp, "Edition", "Title", "Year"
  FROM old."Reference", old."ReferenceAuthor"
  WHERE old."Reference"."ReferenceType" = 'Book'
      AND old."Reference"."Year" IS NOT NULL
      AND old."Reference"."Title" IS NOT NULL
      AND old."ReferenceAuthor"."ReferenceID" = old."Reference"."ID"
      --Year, Title and Author must be present in the data, otherwise the entry is deemed useless, hence won't be included
  ON CONFLICT (citavi_id) DO UPDATE 
      SET (abstract, createdon, edition, title, year) = (excluded.abstract, excluded.createdon, excluded.edition, excluded.title, excluded.year)
  ; 
  </code></pre>
  
  <p>I added an additional source in the FROM statement and one more WHERE statement to make sure only entries that have a title, year and author are inserted into the new database. (If old."Reference"."ID" exists in old."ReferenceAuthor" as "ReferenceID", then an author exists.) Even without the additional WHERE statement the query is faulty. The columns I specified in SELECT are only present in <code>old."Reference"</code>, not in <code>old."ReferenceAuthor"</code>. 
  Currently <code>old."ReferenceAuthor"</code> and <code>old."Reference"</code> don't have a UNIQUE CONSTRAINT,the uniqe constraints for bookmonographs are:</p>
  
  <pre><code>CONSTRAINT bookmonographs_pk PRIMARY KEY (bookmonographsid),
  CONSTRAINT bookmonographs_bookseries FOREIGN KEY (bookseriesid)
        REFERENCES new.bookseries (bookseriesid) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT bookmonographs_citaviid_unique UNIQUE (citavi_id)
  </code></pre>
  
  <p>The error PSQL throws:</p>
  
  <blockquote>
    <p>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.
    ********** Error **********</p>
    
    <p>ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
    SQL state: 21000
    Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.</p>
  </blockquote>
  
  <p>I don't know what's wrong, or why the hint points to a duplicated constrained value.</p>