4

for example: index by userid, sdate, edate

userid  sdate       edate
001     2019-01-01  2019-01-30

if I insert new data like:

userid  sdate       edate           
001     2019-01-03  2019-01-20  
   or
001     2019-01-13  2019-02-10  
   or
001     2019-02-01  2019-02-15

I tried below using GIST, but how to combine it using ON CONFLICT?

CREATE EXTENSION btree_gist

CREATE TABLE test(
   ID INT PRIMARY KEY     NOT NULL,
   USERID     CHAR(5),
   SDATE      DATE,
   EDATE      DATE,
   EXCLUDE USING gist
   (USERID WITH =,
   daterange(SDATE, EDATE, '[]') WITH &&)
);


Insert Into test (usersid, sdate, edate)
Values  (@UsersId, @SDate, @EDate) 
ON Conflict ON CONSTRAINT  test_userid_daterange_excl 
Do Update 
   Set sdate = @SDate, edate = @EDate

I got:

ERROR:  ON CONFLICT DO UPDATE not supported with exclusion constraints

based on the above scenario, I expect, the following:

userid  sdate       edate       I_EXPECT    
001     2019-01-03  2019-01-20  UPDATE because it is in range
001     2019-01-13  2019-02-10  UPDATE because it is in range
001     2019-02-01  2019-02-15  INSERT because it is not in range

select version() shows:

PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Donald
  • 551
  • 2
  • 6
  • 22

1 Answers1

6

You cannot use INSERT ... ON CONFLICT with an exclusion index, like the error message says.

You will have to use code like in example 43.2 from the documentation.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263