Postgresql - How to insert only if specific conditions are met

user_11665953 0
Viewed 152

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.

sql-insert postgresql sql
2 Answers

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.

user_22753880 
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;
user_55241000
Related