Return an error message from a TRIGGER in PostgreSQL

Question

I want to perform some business validation of the data being inserted in a table and I need to check data in another table when doing so. The way to this seems to be using a BEFORE INSERT FOR EACH ROW TRIGGER in PostgreSQL and from the function it calls I can return NULL if the new data fails validation to prevent it being inserted.

Is there any way I can return an error message describing the validation error as well?

In addition to @mu's answer, consider this if you want a context for your own exceptions. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jun 7, 2012 at 22:05

mu is too short · Accepted Answer · 2012-06-07 04:30:28Z

12

Use raise for exceptions:

39.8. Errors and Messages

Use the RAISE statement to report messages and raise errors.
[...]
EXCEPTION raises an error (which normally aborts the current transaction);

So you can say things like this in your trigger:

raise exception 'Say something useful about %', some_variable;

That will abort the trigger and (usually) the current transaction. The message that you raise should make its way back to the client application.

answered Jun 7, 2012 at 4:30

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Neil McGuigan Over a year ago

careful, you can't use NOTIFY in a trigger if there's an exception

Collectives™ on Stack Overflow

Return an error message from a TRIGGER in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related