MIME-Version: 1.0
From: Valerio Battaglia <vabatta@gmail.com>
Date: Sun, 26 Mar 2023 12:54:18 +0200
Message-ID: 
 <CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com>
Subject: How to get column, table or parameter name reporting when violating
 DOMAIN type constraint
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000650d4d05f7cb7386"
Archived-At: 
 <https://www.postgresql.org/message-id/CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA%40mail.gmail.com>
Precedence: bulk

--000000000000650d4d05f7cb7386
Content-Type: text/plain; charset="UTF-8"

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

--000000000000650d4d05f7cb7386
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div>Hi,<br><br></div>I am reaching out to request as=
sistance with obtaining column, table or=20
parameter name when a DOMAIN type constraint is violated in PostgreSQL.=20
Specifically, I am looking for a way to easily identify the column or=20
parameter that is causing the constraint violation, so that I can have bett=
er error / validation reporting in the client.<br><br></div>Imagine the fol=
lowing SQL code:<br><pre><code class=3D"gmail-!whitespace-pre gmail-hljs gm=
ail-language-sql"><span class=3D"gmail-hljs-keyword">CREATE</span> DOMAIN m=
y_domain <span class=3D"gmail-hljs-keyword">AS</span> <span class=3D"gmail-=
hljs-type">integer<br></span> CONSTRAINT value_min <span class=3D"gmail-hlj=
s-keyword">CHECK</span> (<span class=3D"gmail-hljs-keyword">VALUE</span> <s=
pan class=3D"gmail-hljs-operator">&gt;</span> <span class=3D"gmail-hljs-num=
ber">0</span>)<br> CONSTRAINT value_max CHECK (VALUE &lt;=3D 200);

<span class=3D"gmail-hljs-keyword">CREATE</span> <span class=3D"gmail-hljs-=
keyword">FUNCTION</span> my_function(first_parameter my_domain, second_para=
meter my_domain) <span class=3D"gmail-hljs-keyword">RETURNS</span> void <sp=
an class=3D"gmail-hljs-keyword">AS</span> $$
<span class=3D"gmail-hljs-keyword">BEGIN</span>
  <span class=3D"gmail-hljs-keyword">INSERT</span> <span class=3D"gmail-hlj=
s-keyword">INTO</span> my_table (first_column, second_column) <span class=
=3D"gmail-hljs-keyword">VALUES</span> (first_parameter, second_parameter);
<span class=3D"gmail-hljs-keyword">END</span>;
$$ <span class=3D"gmail-hljs-keyword">LANGUAGE</span> plpgsql;<br></code></=
pre><div><div><div>When I call this function with a value that violates the=
 DOMAIN type constraint, I receive the following error message:</div><div><=
pre><code class=3D"gmail-!whitespace-pre gmail-hljs gmail-language-sql">SEL=
ECT my_function(100, -100);<br>-- ERROR:  <span class=3D"gmail-hljs-keyword=
">value</span> <span class=3D"gmail-hljs-keyword">for</span> domain my_doma=
in violates <span class=3D"gmail-hljs-keyword">check</span> <span class=3D"=
gmail-hljs-keyword">constraint</span> &quot;value_min&quot;
</code></pre></div><div><p>However, this error message does not provide any=
 information about which column or parameter is causing the constraint viol=
ation. The same scenario applies when calling the INSERT INTO statement dir=
ectly as well.<br></p><p>Is there a way to obtain more detailed information=
 about the column, table or parameter that is causing the constraint violat=
ion in this scenario? I would greatly appreciate any guidance or advice you=
 could provide on this matter.<br><br></p><p>Thanks<br></p></div></div></di=
v></div>

--000000000000650d4d05f7cb7386--