Postgre如何仅在满足特定条件的情况下插入

Question

我有下表：

create table foobar
(
account_id  bigint    not null,
timestamp   timestamp not null,
balance_one numeric   not null,
balance_two numeric   not null,
primary key (timestamp, account_id),
);

目前，我正在使用以下语句一次将数千条记录插入该表：

insert into foobar(account_id, timestamp, balance_one, balance_two)
VALUES (?, ?, ?, ?), (?, ?, ?, ?), ETC...
on conflict DO NOTHING;

我想给insert语句添加一个约束，如果new.balance_one == old.balance_one AND new.balance_two == old.balance_two，则停止插入新记录其中old=最近的(时间戳(记录，其中account_id=new.account_id.

我该怎么做？插入性能也是一个问题。

Answer 1

也许您应该看看ON INSERT触发器。你可以创建一个函数：

CREATE OR REPLACE FUNCTION check_insert() RETURNS TRIGGER AS $$
DECLARE rec foobar;
BEGIN
SELECT balance_one,balance_two FROM foobar INTO rec.balance_one,rec.balance_two
WHERE account_id = NEW.account_id
ORDER BY timestamp DESC LIMIT 1;    
IF rec.balance_one = NEW.balance_one AND
rec.balance_two = NEW.balance_two THEN
RETURN OLD; -- this will silently ignore the insert for the current record
ELSE
RETURN NEW;
END IF;
END; $$ LANGUAGE plpgsql;

并将其附在您的桌子上

CREATE TRIGGER trg_check_balances BEFORE INSERT ON foobar
FOR EACH ROW EXECUTE PROCEDURE check_insert();

演示：db<>fiddle

注意：如果您试图在同一事务中插入两个具有相同acount_id的记录，此方法将失败，因为事务中的所有记录都将获得相同的timestamp，并且您的主键将被违反。

Answer 2

INSERT INTO foobar(account_id, timestamp, balance_one, balance_two)
WITH oldies AS (
SELECT DISTINCT ON (account_id)
account_id, timestamp, balance_one, balance_two
FROM foobar
ORDER BY account_Id, timestamp DESC
)
SELECT n.*
FROM (VALUES (?, ?, ?, ?), (?, ?, ?, ?), ...)
AS n(account_id, timestamp, balance_one, balance_two)
LEFT JOIN oldies o
ON n.account_id = o.account_id
WHERE o.account_id IS NULL OR NOT (
n.balance_one = o.balance_one
AND
n.balance_two = o.balance_two
)
ON CONFLICT DO NOTHING;