Postgresql – How to insert only if specific conditions are met

I have the following table:

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),
);

Currently, I’m inserting thousands of records into this table at once using the following statement:

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

I want to add a constraint to the insert statement, stopping new records being inserted if new.balance_one == old.balance_one AND new.balance_two == old.balance_two where old = the most recent (timestamp) record where account_id = new.account_id.

How do I do this? Insert performance is also a concern.

Answer

Perhaps you should take a look at ON INSERT triggers. You could create a function :

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;

… and attach it to your table

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

Demo: db<>fiddle

Note: This approach will fail if you try to insert two records with the same acount_id in the same transaction, as all records in the transaction will get the same timestamp and your primary key will be violated.