title: PostgreSQL using timestamp difference in partial index for upsert
body: <p>I need to get real-time data and put it into a Postgres table so compare the columns oid and rcv_time respectively with newly received ones.</p>
  
  <p><strong>If this oid previously has been inserted and its received time is more than two hours from now should be inserted otherwise only need to be updated based on oid</strong> </p>
  
  <p>So I want to create a partial index like below which indicates timestamp difference as conditional unique constraint:</p>
  
  <pre><code>CREATE UNIQUE INDEX oid_uqidx ON my_table (oid,rcv_time) where EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rcv_time)) / 3600 &lt; 2;
  </code></pre>
  
  <p>and as a sample my upsert query would be :</p>
  
  <pre><code>INSERT INTO my_table (oid, rcv_time, name)
  VALUES ('730048b','2020-04-24 02:46:00','test') 
  ON CONFLICT ON CONSTRAINT oid_uqidx
  DO UPDATE SET (rcv_time,name) = (EXCLUDED.rcv_time,EXCLUDED.name);
  </code></pre>
  
  <p>But when I try to create index the following error occurs:</p>
  
  <pre><code>ERROR: functions in index predicate must be marked IMMUTABLE
  
  </code></pre>
  
  <p>I also tried to work around without partial index by putting the where clause in upsert query
  and instead, create a unique constraint on oid.</p>
  
  <pre><code>INSERT INTO my_table (oid, rcv_time, name)
  VALUES ('730048b','2020-04-24 02:46:00','test') 
  ON CONFLICT(oid) where EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rcv_time)) / 3600 &lt; 2
  DO UPDATE SET (rcv_time,name) = (EXCLUDED.rcv_time,EXCLUDED.name);
  </code></pre>
  
  <p>But It doesn't let me have multiple same oid and always do the update.</p>
  
  <p>How can I approach the problem?</p>