2

I have a PostgreSQL 12 database, and in a table I created a column with a generated column, then I have a before update trigger that fires only if the NEW value is DISTINCT from OLD value, essentially this:

BEFORE UPDATE 
ON public.some_table
FOR EACH ROW
WHEN (NEW IS DISTINCT FROM OLD)
EXECUTE PROCEDURE public.update_modified_column();

However, this code despite being correct does not work with generated columns, giving the following error:

BEFORE trigger's WHEN condition cannot reference NEW generated columns

So my question is this: Is there any way to use NEW with a table that has generated columns without having to specify every single column of that table that isn't generated?

It can be something that requires specifying the columns that were generated to be excluded, just not the other way around.

Also, I know a potential alternative to generated tables is views (which are not the same thing but close enough for my use case), but I can't use views, since I need something that keeps track if the columns allows or doesn't allow for null values, which unfortunately views do not do that.

Using normal columns with a before insert/update trigger to calculate the columns, along with setting the columns as NOT NULL with a Default (for my use case ofc), has essentially fixed the issue; however, I will keep the question open to see if there is any solution to the generated columns specifically, since they are a bit more of an elegant solution.

0

2 Answers 2

2

That is simple: use an AFTER trigger instead. The column value will have been computed then.

On the other hand, your answers reveals that you need a BEFORE trigger because you want to modify NEW.

So probably the best solution is to use a BEFORE trigger with a WHEN condition that excludes the generated column:

WHEN ((NEW.col1, NEW.col2, ...)
      IS DISTINCT FROM
      (OLD.col1, OLD.col2, ...))
2
  • I have not tried your example, but wouldn't doing that specifically require the trigger to mention every single non generated column, which is a possibility I mentioned in my question. If, so I believe this solution has 1 major issue, because it is very easy to break the trigger by adding a new column, since one would need to remember to also change the trigger.
    – Micael
    Aug 25, 2020 at 13:26
  • Yes, that is true. I personally would still prefer that over updating in an AFTER trigger, but yours is a valid concern. Aug 25, 2020 at 13:45
1

After Laurenz Albe suggestion of using the AFTER TRIGGER, I revisited the use of it to see why exactly it hadn't worked when I tried it before, and found out that NEW and OLD cannot be used in trigger functions called by an after trigger to modify such values (which is described in the documentation). So a function that works in a before update trigger will not work in an after update trigger, if it is using NEW/OLD to change values, which means the action NEW.modified = now() did not work, despite not throwing any errors.

Due to this I have modified my update_modified_column() function to be like this:

BEGIN
EXECUTE format('UPDATE %I.%I SET modified = now() WHERE id= %L', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id);
RETURN NEW;
END;

This does work as expected, however besides requiring a column named modified to exist, it also now requires a unique column called id to be there as well, so not quite as simple and versatile as the old one. It should be possible to obtain the primary key programmatically, however for my use case this is good enough since all my tables use a field called id.

Either way for my own personal use I consider the question as answered, since there are both 2 solutions to the problem, and an explanation why generated columns wouldn't work with a BEFORE UPDATE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.