Is it correct to raise an exception in a domain check

On Tue, Jun 10, 2025 at 4:53 PM Logan Grosz <logan.grosz@gmail.com> wrote:

Hi,

I have a `DOMAIN` on `JSONB`. I need to verify the shape of the JSON, so I
have a check expression written in PL/pgSQL. The docs say

Each constraint must be an expression producing a Boolean result

Would it be correct to raise an exception to indicate a failed check? I
perform type-casts (that throw already) and other checks that would benefit
from attaching a description to the client error.

Since PG will throw an error when any CHECK constraint fails, are you
hoping to provide more details by throwing your own exception?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Logan Grosz <logan.grosz@gmail.com> writes:

I have a `DOMAIN` on `JSONB`. I need to verify the shape of the JSON, so I
have a check expression written in PL/pgSQL. The docs say

Each constraint must be an expression producing a Boolean result

Would it be correct to raise an exception to indicate a failed
check?

It'd be better just to have the CHECK expression return false.
Admittedly, that's usually just going to end in an exception,
but pre-judging that inside the expression doesn't seem ideal.
An example of why not is that you'd break "soft" input error
handling in COPY.

As you say, there are some cases where it's hard to avoid
an exception, but I'm not sure that "better error message"
is a good justification for throwing one. Still, in the
end it's your own judgment to make.

regards, tom lane