title: Postgres SQL error: there is no unique or exclusion constraint matching the ON CONFLICT specification" DESPITE constrain being set
body: <p>(sorry, there are many similar questions on SO but none I could find that match well enough)</p>
  <p>Attempting to upsert to a Postgres RDS table via a temp table...</p>
  <pre><code>import sqlalchemy as sa
  
  # assume db_engine is already set up
  
  with db_engine.connect() as conn:
  
              conn.execute(sa.text(&quot;DROP TABLE IF EXISTS temp_table&quot;))
              build_temp_table = f&quot;&quot;&quot;
                  CREATE TABLE temp_table (
                  unique_id        VARCHAR(40) NOT NULL,
                  date             TIMESTAMP,
                  amount           NUMERIC,
                  UNIQUE (unique_id)
                  );
                  &quot;&quot;&quot;
              conn.execute(sa.text(build_temp_table))
  
              upsert_sql_string = &quot;&quot;&quot;
                  INSERT INTO production_table(unique_id, date, amount)
                  SELECT unique_id, date, amount FROM temp_table
                  ON CONFLICT (unique_id)
                  DO UPDATE SET 
                              date = excluded.date,
                              amount  = excluded.amount
                  &quot;&quot;&quot;
              conn.execute(sa.text(upsert_sql_string))
  </code></pre>
  <p>Note: <code>production_table</code> is configured the identically to <code>temp_table</code></p>
  <p>Other methods I have tried include:</p>
  <ul>
  <li>Specifying <code>unique_id</code> as PRIMARY KEY or <code>UNIQUE</code> in table definition</li>
  <li>Running <code>ALTER TABLE temp_table ADD PRIMARY KEY (unique_id)</code> after creating <code>temp_table</code></li>
  </ul>
  <p>Regardless of what I do, I get the error:</p>
  <p><code>psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification</code></p>
  <p>Thanks</p>