Thread: Own messages for constraints?
Hi ! Is it possible in postgres to define own message for some constraint? Example: If i create check constraint on zip_code column , i want to get a message: "Zip code is invalid.Please,input code in format: nn-nnn" and I want to send this message to the end user. It will be much better(in my case) than "violates constraint zip_code_check" :-) . I can make this validation in trigger (and send msg to application by notice or raise),but in this case i will duplicate validation rules (in trigger and in constraint).Effect: Lower performance(i think) and rules in two places... Best regards and thanks for help, Kacper Chrapa ---------------------------------------------------- Fantastyczna promocja w RUCHu! Rób zakupy, wysyłaj SMSy i codziennie wygrywaj bony pieniężne! W finale do wygrania Nissan NOTE. Sprawdź: http://klik.wp.pl/?adr=www.loteriaruch.pl&sid=1062
On Fri, 2007-03-16 at 20:19 +0100, Kacper Chrapa wrote:
> Hi !
>
> Is it possible in postgres to define own message for some constraint?
>
> Example:
> If i create check constraint on zip_code column , i want to get
> a message: "Zip code is invalid.Please,input code in format: nn-nnn"
> and I want to send this message to the end user.
> It will be much better(in my case) than "violates constraint
> zip_code_check" :-) .
>
> I can make this validation in trigger (and send msg to application by
> notice or raise),but in this case i will duplicate validation
> rules (in trigger and in constraint).Effect: Lower performance(i think)
> and rules in two places...
>
There's no custom message for a CHECK constraint violation.
You can use an AFTER trigger instead of a CHECK constraint (but that may
have a performance impact - test for your application).
You can also give descriptive names to your CHECK constraint which may
help.
It's not a good idea to pass database errors directly back to the user
anyway.
Regards,
Jeff Davis
On 3/19/07, Jeff Davis <pgsql@j-davis.com> wrote: > You can use an AFTER trigger instead of a CHECK constraint (but that may > have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. depesz -- http://www.depesz.com/ - nowy, lepszy depesz
On Mon, Mar 19, 2007 at 07:08:41PM +0100, hubert depesz lubaczewski wrote: > On 3/19/07, Jeff Davis <pgsql@j-davis.com> wrote: > >You can use an AFTER trigger instead of a CHECK constraint (but that may > >have a performance impact - test for your application). > > are you sure you meant AFTER? why? generally data-checks should be in > before triggers. i guess. In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On 3/19/07, Martijn van Oosterhout <kleptog@svana.org> wrote: > In an AFTER trigger you can be sure you're seeing what actually got > inserted. In a BEFORE trigger other triggers after you could still > modify the data... yes but in after trigger the only thing you can do is to raise exception. you cannot fix the data, issue warning, or simply stop the insert/update without breaking the transaction. depesz
On Mon, 2007-03-19 at 19:08 +0100, hubert depesz lubaczewski wrote:
> On 3/19/07, Jeff Davis <pgsql@j-davis.com> wrote:
> > You can use an AFTER trigger instead of a CHECK constraint (but that may
> > have a performance impact - test for your application).
>
> are you sure you meant AFTER? why? generally data-checks should be in
> before triggers. i guess.
>
If you do the check BEFORE, you have to make sure that no other BEFORE
triggers that execute afterward modify the data again.
Assuming your AFTER trigger is on INSERT and UPDATE, there is no way for
a subsequent AFTER trigger to modify the data to be invalid. So an AFTER
trigger is more of an assurance that your data is valid.
Note that AFTER triggers need to queue up, so if you do a huge update
and have an AFTER trigger, it might use a lot of memory. BEFORE triggers
don't have that problem. If you're very concerned about this you could
use a BEFORE trigger and just make sure that no other trigger will cause
a problem.
Regards,
Jeff Davis
On Mon, 2007-03-19 at 19:26 +0100, hubert depesz lubaczewski wrote:
> On 3/19/07, Martijn van Oosterhout <kleptog@svana.org> wrote:
> > In an AFTER trigger you can be sure you're seeing what actually got
> > inserted. In a BEFORE trigger other triggers after you could still
> > modify the data...
>
> yes but in after trigger the only thing you can do is to raise
> exception. you cannot fix the data, issue warning, or simply stop the
> insert/update without breaking the transaction.
>
If you only issue a warning, it's not a constraint because data
violating the constraint still goes in. And you can issue a warning in
an AFTER trigger.
Fixing the data is probably something that should be done in a different
place (like the application correcting the data). It also begs the
question: If the data can be fixed, why is the original form not
acceptable anyway (i.e. fixed in the datatype's input function)?
I assume by "stop the insert/update without breaking the transaction"
you mean a return NULL from the BEFORE trigger, thereby not inserting
the row. COMMIT should mean "yes, I successfully completed what you
asked," and that usually means that the data was actually inserted.
You're correct that you have more flexibility with a BEFORE trigger in
many ways. However, be careful using those strategies to constrain data.
Generally you do want it to break the transaction if the data you're
trying to insert is invalid.
Regards,
Jeff Davis