Accessing old and new values in WHERE clause of CREATE RULE

Question

I'm trying to merge two dump files as outlined in this answer. In working through creating the rule I keep having issues trying to access both the OLD and NEW values of my account table.

The account schema is just a table with a single column of type jsonb called value. Inside the value column are two fields off which I want to create the skip_unique rule. Following is the latest query I've tried and the ensuing response:

CREATE OR REPLACE RULE skip_unique AS ON INSERT TO account WHERE NEW.value->>'opCo' = OLD.value->>'opCo' AND NEW.value->>'customerId' = OLD.value->>'customerId' DO INSTEAD NOTHING;

ERROR:  invalid reference to FROM-clause entry for table "old"
LINE 1: ...S ON INSERT TO account WHERE NEW.value->>'opCo' = OLD.value-...
                                                             ^
HINT:  There is an entry for table "old", but it cannot be referenced from this part of the query.

I've tried numerous permutations of the same general query including:

CREATE OR REPLACE RULE skip_unique AS ON INSERT TO account WHERE (EXISTS (SELECT 1 FROM account WHERE NEW.value->>'opCo' = OLD.value->>'opCo' AND NEW.value->>'customerId' = OLD.value->>'customerId')) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE skip_unique AS ON INSERT TO account WHERE (EXISTS (SELECT 1 FROM account AS b WHERE b.value->>'opCo' = account.value->>'opCo' AND b.value->>'customerId' = account.value->>'customerId')) DO INSTEAD NOTHING;
CREATE OR REPLACE RULE skip_unique AS ON INSERT TO account old WHERE (NEW.value->>'opCo' = old.value->>'opCo' AND NEW.value->>'customerId' = old.value->>'customerId') DO INSTEAD NOTHING;

None of them have worked for various reasons.

Thanks in advance for your help!

the answer is in the link you provide. use where exists and the column name (without old) for the current data and new.column name for the new one. — JGH, Jun 08 '17 at 15:07

score 2 · Accepted Answer · answered Jun 08 '17 at 15:05

There is no OLD variable (or rather it's unassigned) on an INSERT trigger. After all, with an INSERT you're inserting something new, so what would you expect OLD to hold?

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

You should be looking for the value in the table rather than OLD.

That makes so much sense. Thank you! – davidicus Jun 08 '17 at 15:30 — davidicus, Jun 08 '17 at 15:30

Accessing old and new values in WHERE clause of CREATE RULE

1 Answers1

OLD