Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

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;
  ELSE
    RETURN NEW;
 END IF;
END; $$ LANGUAGE plpgsql;

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


CREATE TABLE



CREATE FUNCTION



CREATE TRIGGER


INSERT INTO foobar VALUES (2,current_timestamp,1,1);

INSERT 0 1


INSERT INTO foobar VALUES (1,current_timestamp,1,1);
INSERT INTO foobar VALUES (2,current_timestamp,1,1); -- conflict!
INSERT INTO foobar VALUES (3,current_timestamp,1,1);

INSERT 0 1



INSERT 0 0



INSERT 0 1


SELECT * FROM foobar ORDER BY account_id;


account_id
timestamp
balance_one
balance_two


1
2024-10-02 05:38:49.905925
1
1

2
2024-10-02 05:38:49.889864
1
1

3
2024-10-02 05:38:49.905925
1
1



SELECT 3

account_id	timestamp	balance_one	balance_two
1	2024-10-02 05:38:49.905925	1	1
2	2024-10-02 05:38:49.889864	1	1
3	2024-10-02 05:38:49.905925	1	1