PostgreSQL 表比较

Question

我有一张 table :

CREATE TABLE my_schema.my_data
(
    id character varying COLLATE pg_catalog."default" NOT NULL,
    name character varying COLLATE pg_catalog."default" NOT NULL,
    length numeric(6,4),
    width numeric(6,4),
    rp numeric(4,2),
    CONSTRAINT id_pkey PRIMARY KEY (id)
);

和一个临时表:

CREATE TEMPORARY TABLE new_data (LIKE my_schema.my_data);

然后，临时表将填充 my_data 表中存在的最新版本的数据集。

我正在尝试识别临时表中的记录，这些记录与 my_data 表中的现有记录具有相同的主键，但至少有一个不同的其他值。

我目前的方法是运行类似于此示例的查询:

SELECT temp.id 
FROM (SELECT * FROM my_schema.my_data WHERE my_data.id IN ('X2025','X8716','X4091','X2443','X8922','X5929','X3016','X3036','X4829','X9578')) AS orig 
RIGHT JOIN (SELECT * FROM pg_temp.new_data WHERE new_data.id IN ('X2025','X8716','X4091','X2443','X8922','X5929','X3016','X3036','X4829','X9578')) AS temp 
ON (orig.id = temp.id OR (orig.id IS NULL AND temp.id IS NULL))
AND (orig.name = temp.name OR (orig.name IS NULL AND temp.name IS NULL))
AND (orig.length = temp.length OR (orig.length IS NULL and temp.length IS NULL))
AND (orig.width = temp.width OR (orig.width IS NULL and temp.width IS NULL))
AND (orig.rp = temp.rp OR (orig.rp IS NULL and temp.rp IS NULL)) 
WHERE orig.id IS NULL;

这看起来效率很低，如果有更多的列并且我正在迭代大约 10,000 条记录的批处理，我在更大的表上看不到很好的响应时间。

有什么建议可以更有效地识别不同的记录吗？

更新:

我有一个定期更新的数据集。不幸的是，我每次都得到完整的数据集，而不仅仅是新的或更新的记录。 (我正在努力在未来修复这个过程。)目前我只想更新我的表以匹配每天最新的数据提取。我完成了一个过程来处理这些比较和更新，但速度非常慢。我的数据库表包含当前正在使用触发器填充的 import_date 和 modified_date 列。通过触发器，每个 INSERT 语句都使用 current_date 作为这些记录的 import_date 和 modified_date。此外，modified_date 通过触发器 BEFORE UPDATE 设置为 current_date。因此，我只想用最近的数据拉取更新实际经历过数据更改的记录。否则，modified_date 列将变得毫无用处，因为我无法确定该记录的值最近更改的时间。

当前表:ORIG

(实表约100万条记录)

| 导入日期 | 修改日期 | id | 姓名 | 长度 | 宽度 | rp |

| 2018-08-17 | 2018-08-17 | 87 | 蓝色 | 12.0200| 8.0503| 1.82 |

| 2018-08-17 | 2018-08-17 | 88 |红色 | 11.0870| 2.0923| 1.72 |

| 2018-08-17 | 2018-08-17 | 89 |粉色 | 15.0870| 7.9963| 0.95 |

临时表:TEMP

(还包含大约 100 万条记录。将包含当前表中存在的所有主键(id 列)，但也可能包含新的主键。)

| 导入日期 | 修改日期 | id | 姓名 | 长度 | 宽度 | rp |

|空 |空 | 87 | 青色 | 12.0200| 8.0503| 1.82 |

|空 |空 | 88 |红色 | 11.0870| 2.0923| 1.72 |

|空 |空 | 89 |粉色 | 15.0870| 7.9963| 0.95 |

使用上面的示例数据，我希望只有第一个记录，id 87 被更新。之后我的 table 看起来像:

| 导入日期 | 修改日期 | id | 姓名 | 长度 | 宽度 | rp |

| 2018-08-17 | 2018-09-12 | 87 | 青色 | 12.0200| 8.0503| 1.82 |

| 2018-08-17 | 2018-08-17 | 88 |红色 | 11.0870| 2.0923| 1.72 |

| 2018-08-17 | 2018-08-17 | 89 |粉色 | 15.0870| 7.9963| 0.95 |

对我有用的:我更新了 modified_date 触发器函数以识别何时需要新的修改日期:

CREATE FUNCTION my_schema.update_mod_date()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$
DECLARE
BEGIN
    IF tg_op = 'INSERT' THEN
        NEW.modified_date := current_date;
    ELSIF tg_op = 'UPDATE' THEN 
        IF NEW.name IS DISTINCT FROM OLD.name
        OR NEW.length IS DISTINCT FROM OLD.length
        OR NEW.width IS DISTINCT FROM OLD.width
        OR NEW.rp IS DISTINCT FROM OLD.rp THEN
            NEW.modified_date := current_date;
        ELSE
            NEW.modified_date := OLD.modified_date;
        END IF;
    END IF;
    RETURN NEW;
END;
$BODY$;

然后我能够使用@EvanCarroll 提出的原始解决方案:

BEGIN;
INSERT INTO my_schema.my_data (SELECT * FROM pg_temp.new_data) 
ON CONFLICT (id) DO UPDATE SET modified_date=NULL, id=EXCLUDED.id,
name=EXCLUDED.name, length=EXCLUDED.length, width=EXCLUDED.width,
rp=EXCLUDED.rp;
COMMIT;

这确保了 modified_date 仅在行中的其他值之一发生更改时才会更改。

Answer 1

@EvanCarroll Yes, the end goal is to update the original table using the new dataset. – Nathan Scheiderer 41 mins ago

那么你不想这样做。你想改用 INSERT ... ON CONFLICT DO UPDATE .这就是您在 PostgreSQL 中更新插入的方式。

更新

如果您有一行类似 modified_time 的行，您只想在行更新时更新，请使用触发器处理它。 Like this .然后你就这样写下面，

INSERT INTO foo
SELECT *
FROM bar
WHERE NOT EXISTS (
  SELECT 1
  FROM foo
  WHERE foo.x = bar.x
    AND NOT foo.whatever = bar.whatever
);

现在它不会接受行上的更新，除非 whatever 对于每个 x 都是不同的。理想情况下你不会那样做。如果行必须通过 whatever 唯一，我会将其添加到索引中。