public inbox for [email protected]  

help / color / mirror / Atom feed

From: Valerio Battaglia <[email protected]>
To: [email protected]
Subject: How to get column, table or parameter name reporting when violating DOMAIN type constraint
Date: Sun, 26 Mar 2023 12:54:18 +0200
Message-ID: <CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com> (raw)

Hi,

I am reaching out to request assistance with obtaining column, table or
parameter name when a DOMAIN type constraint is violated in PostgreSQL.
Specifically, I am looking for a way to easily identify the column or
parameter that is causing the constraint violation, so that I can have
better error / validation reporting in the client.

Imagine the following SQL code:

CREATE DOMAIN my_domain AS integer
 CONSTRAINT value_min CHECK (VALUE > 0)
 CONSTRAINT value_max CHECK (VALUE <= 200);
CREATE FUNCTION my_function(first_parameter my_domain,
second_parameter my_domain) RETURNS void AS $$BEGIN
  INSERT INTO my_table (first_column, second_column) VALUES
(first_parameter, second_parameter);END;
$$ LANGUAGE plpgsql;

When I call this function with a value that violates the DOMAIN type
constraint, I receive the following error message:

SELECT my_function(100, -100);
-- ERROR:  value for domain my_domain violates check constraint "value_min"

However, this error message does not provide any information about which
column or parameter is causing the constraint violation. The same scenario
applies when calling the INSERT INTO statement directly as well.

Is there a way to obtain more detailed information about the column, table
or parameter that is causing the constraint violation in this scenario? I
would greatly appreciate any guidance or advice you could provide on this
matter.

Thanks

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint
  In-Reply-To: <CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox