OnConflictExpr*onconflict=root->parse->onConflict;

Indexvarno;

RangeTblEntry*rte;

Relationrelation;

OidindexOidFromConstraint=InvalidOid;

rte=rt_fetch(root->parse->resultRelation,root->parse->rtable);

varno=root->parse->resultRelation;

rte=rt_fetch(varno,root->parse->rtable);

relation=table_open(rte->relid,NoLock);

idxExprs=RelationGetIndexExpressions(idxRel);

if (idxExprs&&varno!=1)

ChangeVarNodes((Node*)idxExprs,1,varno,0);

foreach(el,onconflict->arbiterElems)

{

InferenceElem*elem= (InferenceElem*)lfirst(el);

predExprs=RelationGetIndexPredicate(idxRel);

if (predExprs&&varno!=1)

ChangeVarNodes((Node*)predExprs,1,varno,0);

if (!predicate_implied_by(predExprs, (List*)onconflict->arbiterWhere, false))

gotonext;

-- insert...on conflict do unique index inference

--

create table insertconflicttest(key int4, fruit text);

-- These things should work through a view, as well

create view insertconflictview as select * from insertconflicttest;

--

-- Test unique index inference with operator class specifications and

-- named collations

-> Result

(4 rows)

explain (costs off) insert into insertconflictview values(0, 'Crowberry') on conflict (lower(fruit), key, lower(fruit), key) do nothing;

QUERY PLAN

-------------------------------------------------

Insert on insertconflicttest

Conflict Resolution: NOTHING

Conflict Arbiter Indexes: both_index_expr_key

-> Result

(4 rows)

explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on conflict (key, fruit) do update set fruit = excluded.fruit

where exists (select 1 from insertconflicttest ii where ii.key = excluded.key);

QUERY PLAN

-- Succeeds

insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' do update set fruit = excluded.fruit;

insert into insertconflicttest values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing;

insert into insertconflictview values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and fruit = 'inconsequential' do nothing;

-- fails

insert into insertconflicttest values (23, 'Blackberry') on conflict (key) do update set fruit = excluded.fruit;

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

drop index plain;

-- Cleanup

drop view insertconflictview;

drop table insertconflicttest;

--

-- Verify that EXCLUDED does not allow system column references. These

createtableinsertconflicttest(key int4, fruittext);

createviewinsertconflictviewasselect*from insertconflicttest;

explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (key, fruit) do nothing;

explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (fruit, key, fruit, key) do nothing;

explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (lower(fruit), key,lower(fruit), key) do nothing;

explain (costs off)insert into insertconflictviewvalues(0,'Crowberry')on conflict (lower(fruit), key,lower(fruit), key) do nothing;

explain (costs off)insert into insertconflicttestvalues(0,'Crowberry')on conflict (key, fruit) doupdateset fruit=excluded.fruit

where exists (select1from insertconflicttest iiwhereii.key=excluded.key);

insert into insertconflicttestvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry' doupdateset fruit=excluded.fruit;

insert into insertconflicttestvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry'and fruit='inconsequential' do nothing;

insert into insertconflictviewvalues (23,'Blackberry')on conflict (key)where fruitlike'%berry'and fruit='inconsequential' do nothing;

insert into insertconflicttestvalues (23,'Blackberry')on conflict (key) doupdateset fruit=excluded.fruit;

dropindex plain;

dropview insertconflictview;

droptable insertconflicttest;