PostgreSQL : IF UPDATING ( COLUMN NAME ) equivalent in postgres

Question

I am doing a Oracle to EDB Migration. In oracle i have used :

IF UPDATING(column name) and :OLD.value <> :NEW.value THEN

But I am unable to use the syntax in Postgres and need to make changes. Kindly suggest how to implement the functionality in EDB.

There is no equivalent. You can only define the trigger to only fire if that column was updated. — , Dec 17 '18 at 23:04
@a_horse_with_no_name then unlike in Oracle where we can write column based updates in a single trigger, do i need to write separate trigger for each column in Postgres ? — urs_ng, Dec 17 '18 at 23:12
Thanks @a_horse_with_no_name, i tested with the case in that format and its working fine. — urs_ng, Dec 17 '18 at 23:27
better to use `IF OLD.name IS DISTINCT FROM NEW.name THEN` - it is null safe — Pavel Stehule, Dec 18 '18 at 04:51

score 1 · Accepted Answer · answered Dec 18 '18 at 16:50

1

If you use row trigger, then you can write

IF OLD.column_name IS DISTINCT FROM NEW.column_name THEN

answered Dec 18 '18 at 16:50

Pavel Stehule

what if the same value is applied to the column? Then this statement will also be true. – Enrico Miranda Jun 15 '22 at 07:32
@EnricoMiranda, no - then it returns false. But in this case, there is not real update. There is not possibility to identify updated column from SQL level. PostgreSQL allows more before triggers per table, and any trigger can modify any field of row. Is not possible to distinct if field was modified by SQL or inside trigger. – Pavel Stehule Jun 15 '22 at 11:22

1 Answers1