2

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.

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

So I want to create a partial index like below which indicates timestamp difference as conditional unique constraint:

CREATE UNIQUE INDEX oid_uqidx ON my_table (oid,rcv_time) where EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - rcv_time)) / 3600 < 2;

and as a sample my upsert query would be :

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);

But when I try to create index the following error occurs:

ERROR: functions in index predicate must be marked IMMUTABLE

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.

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 < 2
DO UPDATE SET (rcv_time,name) = (EXCLUDED.rcv_time,EXCLUDED.name);

But It doesn't let me have multiple same oid and always do the update.

How can I approach the problem?

fatemeh poormohammad
  • 62
  • 9
  • A partial index can't be used in an `on conflict()` clause to begin with. –  Jun 04 '20 at 09:34
  • So what should I use to meet my requirement? @a_horse_with_no_name I used this tutorial [Upsert](https://www.postgresqltutorial.com/postgresql-upsert/) – fatemeh poormohammad Jun 04 '20 at 09:40

0 Answers0