sql - 批量插入数据计算新插入

Question

假设我有一个简单的架构，其中包含一个带列的复合 pk。例如

pk1: 字符串

pk2: 整数

日期:时间戳

我正在以大约 50 个批处理从其他地方读取数据，并想存储这些数据。我从中提取的数据源是一个滑动窗口，所以我将从我已经插入的数据源接收数据，所以我不能盲目插入，否则我会违反 pk 约束。

我想要一种合理的方式来批量插入新项目，但也希望知道为了记录目的我实际插入了多少新项目。

Answer 1

插入

对于 postgresql 版本 9.5+ ，可以使用以下内容:

insert ... on conflict do nothing

例子:

INSERT INTO users (id, user_name, email) 
VALUES (1, 'hal', 'hal@hal.hal') 
ON CONFLICT DO NOTHING

对于最近的早期版本(从 9+ 开始，我认为)，可以从原始值创建 CTE，然后从那里插入:

WITH batch (id, user_name, email) AS (
VALUES
  (1, 'hal', 'hal@hal.hal'),
  (2, 'sal', 'sal@sal.sal')
)
INSERT INTO users (id, user_name, email) (
SELECT id, user_name, email
FROM batch
WHERE batch.id NOT IN (SELECT id FROM users)
)

或者，不使用 CTE，而是将值暂存在暂存表中，该暂存表在处理每个批处理后被截断。

另请注意，如果使用 CTE 方法，可能需要将字符串显式转换为适当的数据类型。

第三种选择是使用存储过程和触发器来实现。这比其他两个更复杂，但可以与早期版本的 postgresql 一起使用。

记录

这两种方法都应报告插入的行数，但日志记录必须由数据库客户端执行。

例如在 Python 中，库 psycopg2 用于与 postgresql 交互，psycopg2 游标对象有一个属性 rowcount。我确信用其他语言/框架编写的其他设计良好的库将以某种方式实现相同的功能。记录插入的行数必须从与数据库交互的程序部分完成。

但是，如果在同一个数据库中需要插入多少行的日志，那么可以通过单个触发器+存储过程来执行更新插入和日志记录。

最后，由于这是upsert的特例，更多信息可以在stack overflow或其他网站上搜索postgresql upsert找到。我从 postgresql wiki 中发现以下内容非常有用:

https://wiki.postgresql.org/wiki/UPSERT#PostgreSQL_.28today.29