title: My PSQL after insert trigger fails to insert into another table when ON DUPLICATE encounters a dupilcate
body: <p>I am slowly working through a feature where I am importing large csv files. The contents of the csv file has a chance that when it is uploaded the contents will trigger a uniqueness conflict. I've combed stack overflow for some similar resources but I still can't seem to get my trigger to update another table when a duplicate entry is found. The following code is what I have currently implemented with my line of logic for this process. Also, this is implemented in a rails app but the underlying sql is the following.</p>
  <p>When a user uploads a file, the following happens when its processed.</p>
  <pre><code>
  CREATE TEMP TABLE codes_temp ON COMMIT DROP AS SELECT * FROM codes WITH NO DATA;
  
  create or replace function log_duplicate_code() 
    returns trigger 
    language plpgsql 
    as 
  $$
  begin
      insert into duplicate_codes(id, campaign_id, code_batch_id, code, code_id, created_at, updated_at)
      values (gen_random_uuid(), excluded.campaign_id, excluded.code_batch_id, excluded.code, excluded.code_id, now(), now());
      return null;
  end; 
  $$
  
  create trigger log_duplicate_code 
  after insert on codes 
  for each row execute procedure log_duplicate_code();
  
  INSERT INTO codes SELECT * FROM codes_temp ct 
      ON CONFLICT (campaign_id, code) 
  DO update set updated_at = excluded.updated_at;
  
  DROP TRIGGER log_duplicate_code ON codes;
  </code></pre>
  <p>When I try to run this process nothing happens at all. If I were to have a csv file with this value <code>CODE01</code> and then upload again with <code>CODE01</code> the <code>duplicate_codes</code> table doesn't get populated at all and I don't understand why. There is no error that gets triggered or anything so it seems like <code>DO UPATE.....</code> is doing something. What am I missing here?</p>
  <p>I also have some questions that come to my mind even if this were to work as intended. For example, I am uploading millions of these codes, etc.</p>
  <p>1). Should my trigger be a statement trigger instead of a row for scalability?</p>
  <p>2). What if someone else tries to upload another file that has millions of codes? I have my code wrapped in a transaction. Would a new separate trigger be created? Will this conflict with a previously processing process?</p>
  <p><strong>####### EDIT #1 #######</strong></p>
  <p>Thanks to Adriens' comment I do see that <code>After Insert</code> does not have the <code>OLD</code> key phrase. I updated my code to use <code>EXCLUDED</code> and I receive the following error for the trigger.</p>
  <pre><code>ERROR:  missing FROM-clause entry for table &quot;excluded&quot; (PG::UndefinedTable)
  </code></pre>
  <p>Finally, here are the S.O. posts I've used to try to tailor my code but I just can't seem to make it work.</p>
  <p><strong>####### EDIT #2 #######</strong></p>
  <p>I have a little more context on to how this is implemented.</p>
  <p>When the CSV is loaded, a staging table called <code>codes_temp</code> is created and dropped at the end of the transaction. This table contains no unique constraints. From what I read only the actual table that I want to insert <code>codes</code> should have the unique constraint error.</p>
  <p>In my <code>INSERT</code> statement, the <code>DO update set updated_at = excluded.updated_at;</code> doesn't trigger a unique constraint error. As of right now, I don't know if it should or not. I borrowed this logic taken from this s.o. question <a href="https://stackoverflow.com/questions/39097824/postgresql-log-into-another-table-with-on-conflict">postgresql log into another table with on conflict</a> it seemed to me like I had to update something if I specify the <code>DO UPDATE SET</code> clause.</p>
  <p>Last, the correct criteria for codes in the database is the following:</p>
  <p>For example, this is an example entry in my <code>codes</code> table</p>
  <pre><code>id, campaign_id, code
  1,  1,           CODE01
  2,  1,           CODE02
  3,  1,           CODE03
  </code></pre>
  <p>If any of these codes appear again somewhere, This should not be inserted into the <code>codes</code> table but it needs to be inserted into the <code>duplicate_codes</code> table because they were already uploaded before.</p>
  <pre><code>id, campaign_id, code
  1,  1,           CODE01.  
  2,  1,           CODE02
  3,  1,           CODE03
  </code></pre>
  <p>As for the <code>codes_temp</code> table I don't have any unique constraints, so there is no criteria to select the right one.</p>
  <p><a href="https://stackoverflow.com/questions/39097824/postgresql-log-into-another-table-with-on-conflict">postgresql log into another table with on conflict</a></p>
  <p><a href="https://stackoverflow.com/questions/39950624/postgres-insert-on-conflict-update-using-other-table">Postgres insert on conflict update using other table</a></p>
  <p><a href="https://stackoverflow.com/questions/69336081/postgres-on-conflict-insert-to-another-table">Postgres on conflict - insert to another table</a></p>
  <p><a href="https://stackoverflow.com/questions/39663280/how-to-do-insert-into-select-and-on-duplicate-update-in-postgresql-9-5">How to do INSERT INTO SELECT and ON DUPLICATE UPDATE in PostgreSQL 9.5?</a></p>