这里有一个关于PostGres中基本DB查询的问题。
我创建了两个表,如下所示:
create table Prix (rank integer primary key, value bigint unique);
create table Pris (rank integer primary key, value bigint unique);
字符串
换句话说,等级和价值都必须是唯一的。
这两个表保存数据:
select * from Prix;
型
| 秩|值|
| --|--|
| 1 | 1229 |
| 2 | 1993 |
select * from Pris;
型
| 秩|值|
| --|--|
| 1 | 2719 |
| 2 | 3547 |
我想知道我应该应用的正确查询,以便表Pris不变,表Prix将变为:
select * from Prix;
型
| 秩|值|
| --|--|
| 1 | 1229 |
| 2 | 1993 |
| 3 | 2719 |
| 4 | 3547 |
Prix表现在是合并的结果,并对排名字段进行了调整。
我试着用这些查询(但失败了):
INSERT INTO Prix SELECT * FROM Pris ON CONFLICT DO NOTHING;
INSERT INTO Prix SELECT * FROM Pris ON CONFLICT(rank) SET rank=rank+10;
型
我明白为什么第一个查询不起作用了。
但是第二个也不起作用。所以我一定是用了不正确的语法或者做错了什么。
任何提示或相关信息将不胜感激。
3条答案
按热度按时间t98cgbkg1#
如果 rank 真的只是一个主键,并且您希望 * 调整rank字段 * 以防止其冲突,请将其设置为标识列。这样,您根本无需填充、寻址和维护它。Demos at db<>fiddle:
字符串
您还可以将现有列转换为标识列。只需进行微小的调整,即可使
on conflict
尝试正常工作:型
如果你对身份列过敏,或者只是不想改变你的表,只想改变排名以防止冲突,抓住当前的最高排名,并根据这一点改变其他人:
型
或
型
仍然假设rank只是一个PK,在
row_number()over()
的window子句中,不需要order
任何东西-跳过可以节省一些工作,提高性能,同时实现与row_number()
相同的效果,无论哪种情况,都会从max()
引入的偏移量开始生成一系列唯一的rank。规划器可以检测到子查询是不相关的,因此可以计算一次,其结果被缓存并重用。PostgreSQL不会遭受 dirty read 的困扰,因此在整个语句中,该值将被冻结在MVCC snapshot中-没有任何情况下为每行重新检查它是有意义的。
如果 rank 应该反映 value 从低到高的排名,你可以执行reinsert:
型
它只是从两个表中读取值,将它们合并在一起,并使用
union
进行重复数据删除,然后根据它们当前的asc
结束顺序为它们提供一个新的排名。the demo的末尾显示了调整排名而不重新插入,但它需要两个更新来解决PostgreSQL无法控制的更新顺序。
使用第三个关系可能会更好,一个动态执行此操作的
view
:型
或者一个
materialized view
,每当你告诉它refresh
时,它就会一次又一次地这样做:型
epfja78i2#
为了能够得到您想要的输出,并将数据从“Pris”合并到“Prix”中,同时调整“Prix”中的“rank”字段,我们可以使用一个简单的公共表表达式查询来解决上述问题
查询可以如下所示:
字符串
以下CTE为“merged_data”的查询将为“Pris”表中的每一行计算一个新排名。此新排名是通过将现有排名、整个“Pris”表中的最大排名和1相加生成的。最后一步涉及将计算出的新排名沿着现有“value”列插入“Pris”表中,从而有效地更新目的地表中的“等级”列。
以下问题的进一步说明和实现:https://dbfiddle.uk/kLcxbYNw
希望这对你有帮助。
vecaoik13#
这不会插入重复的值。
并且必须小心,当多个示例运行查询时,该数目将产生错误
| 秩|值|
| --|--|
| 1 | 1229 |
| 2 | 1993 |
| 3 | 2719 |
| 4 | 3547 |
型
fiddle
如果你没有重复的值,它就足够了,
| 秩|值|
| --|--|
| 1 | 1229 |
| 2 | 1993 |
| 3 | 2719 |
| 4 | 3547 |
型
fiddle的