作者
digoal
日期
2021-04-14
标签
PostgreSQL , insert into on conflict
背景
insert into insertconflicttest as t values (23, 'Blackberry') on conflict (key) where fruit like '%berry' and t.fruit = 'inconsequential' do nothing;
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6c0373ab77359c94b279c4e67c91aa623841af65
```
Allow table-qualified variable names in ON CONFLICT ... WHERE.
author Tom Lane tgl@sss.pgh.pa.us
Tue, 13 Apr 2021 19:39:33 +0000 (15:39 -0400)
committer Tom Lane tgl@sss.pgh.pa.us
Tue, 13 Apr 2021 19:39:41 +0000 (15:39 -0400)
commit 6c0373ab77359c94b279c4e67c91aa623841af65
tree a029c1f45655b7100677c3bdfb33bc197042b04c tree
parent e8c435a824e123f43067ce6f69d66f14cfb8815e commit | diff
Allow table-qualified variable names in ON CONFLICT ... WHERE.
Previously you could only use unqualified variable names here.
While that's not a functional deficiency, since only the target
table can be referenced, it's a surprising inconsistency with the
rules for partial-index predicates, on which this syntax is
supposedly modeled.
The fix for that is no harder than passing addToRelNameSpace = true
to addNSItemToQuery. However, it's really pretty bogus for
transformOnConflictArbiter and transformOnConflictClause to be
messing with the namespace item for the target table at all.
It's not theirs to manage, it results in duplicative creations of
namespace items, and transformOnConflictClause wasn't even doing
it quite correctly (that coding resulted in two nsitems for the
target table, since it hadn't cleaned out the existing one).
Hence, make transformInsertStmt responsible for setting up the
target nsitem once for both these clauses and RETURNING.
Also, arrange for ON CONFLICT ... UPDATE's "excluded" pseudo-relation
to be added to the rangetable before we run transformOnConflictArbiter.
This produces a more helpful HINT if someone writes "excluded.col"
in the arbiter expression.
Per bug #16958 from Lukas Eder. Although I agree this is a bug,
the consequences are hardly severe, so no back-patch.
Discussion: https://postgr.es/m/16958-963f638020de271c@postgresql.org
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
