postgres -简化数据库操作

Question

我有下面的表，我需要用Table-2的最新值来更新Table-1，而不需要创建中间临时表。

表-1

| environment | division | item | item_cost |
| ----------- | -------- | ---- | --------- |
| 'local' | 'first' | 'item-U' | 0 |
| 'local-queue' | 'first' | 'item-U' | 0 |
| 'denmark' | 'first' | 'item-U' | 0 |
| 'local' | 'first' | 'item-C' | 0 |
| 'local-queue' | 'first' | 'item-C' | 0 |
| 'denmark' | 'first' | 'item-C' | 0 |
| 'local' | 'second' | 'item-E' | 0 |
| 'local' | 'second' | 'item-L' | 0 |

表-2

| environment | division | item | item_cost |
| ----------- | -------- | ---- | --------- |
| 'denmark' | 'second' | 'item-E' | 1666 |
| 'local' | 'second' | 'item-E' | 4444 |
| 'denmark' | 'first' | 'item-U' | 18080 |
| 'denmark' | 'first' | 'item-U' | 18079 |
| 'local' | 'first' | 'item-U' | 55555 |

是否可以将Table-2合并到Table-1中，使Table-2的最新值低于输出？

| environment | division | item | item_cost |
| ----------- | -------- | ---- | --------- |
| 'local' | 'first' | 'item-U' | 55555 |
| 'local-queue' | 'first' | 'item-U' | 0 |
| 'denmark' | 'first' | 'item-U' | 18080 |
| 'local' | 'first' | 'item-C' | 0 |
| 'local-queue' | 'first' | 'item-C' | 0 |
| 'denmark' | 'first' | 'item-C' | 0 |
| 'local' | 'second' | 'item-E' | 4444 |
| 'local' | 'second' | 'item-L' | 0 |

我有一个传统的方法，它创建一个临时表，然后查找和更新。我只是想知道是否有一种方法可以在不创建临时表的情况下即时获得所需的输出。

表2可能有一些不需要在输出中显示的条目。

谢谢。

Answer 1

您需要考虑使用SQL进行基于集合的操作，并假设这始终是可能的。一个常见的错误是使用两个集合，并试图通过循环集合1来执行更新，以便更新集合2，而不是使用SQL在一个操作中完成所有操作。

我可以回答你的问题，但你的例子不清楚，至少有两个原因：

• 每行的主键或唯一标识符是什么？如果没有定义，你不能合并2个列表。我原以为这是环境、部门和项目。但是你有两行都显示了丹麦，第一，表2中的item-U。
• 你的“下面的输出”没有任何意义。表2中成本为1666的第一个项目不应该显示在最终输出中吗？

假设环境、分区和项目是唯一的，这是合并两组数据的正确方法。尝试插入新记录，如果键已经存在，则改为更新该键的item_cost：

INSERT INTO table_1 (environment, division, item, item_cost)
SELECT environment, division, item, item_cost
FROM table_2
ON CONFLICT (environment, division, item)
DO UPDATE
SET item_cost = EXCLUDED.item_cost;

Postgres 9.5+支持ON CONFLICT。

如果table_2行已经与table_1行相同，为了避免对数据的冗余更新，最好执行以下稍微复杂一些的操作：

-- Only return rows that are actually different from what is already in table_1
WITH actual_data_diff AS
(SELECT environment, division, item, item_cost
FROM table_2
EXCEPT ALL
SELECT environment, division, item, item_cost
FROM table_1)

INSERT INTO table_1 (environment, division, item, item_cost)
SELECT environment, division, item, item_cost
FROM actual_data_diff
ON CONFLICT (environment, division, item)
DO UPDATE
SET item_cost = EXCLUDED.item_cost;

在报告环境中，您通常会得到大量冗余写入，从而导致过度膨胀和WAL流量，这可以通过上面的模式轻松避免，因为上面的模式只允许实际的更改通过。

有一个MERGE语句正在积极地工作，准备与PG12一起发布。