From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Valerio Battaglia <vabatta@gmail.com>,
        "pgsql-novice@lists.postgresql.org"
 <pgsql-novice@lists.postgresql.org>
Subject: Re: How to get column,
 table or parameter name reporting when violating DOMAIN type constraint
In-reply-to: 
 <CAKFQuwYJtF-5Uvqo6ahKkeEAxOKta_yVb3n3at5Y=mvSgC0FrA@mail.gmail.com>
References: 
 <CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com>
 <CAKFQuwYJtF-5Uvqo6ahKkeEAxOKta_yVb3n3at5Y=mvSgC0FrA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sun, 26 Mar 2023 08:13:50 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2911009.1679844383.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 26 Mar 2023 11:26:23 -0400
Message-ID: <2911010.1679844383@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2911010.1679844383%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sunday, March 26, 2023, Valerio Battaglia <vabatta@gmail.com> wrote:
>> Is there a way to obtain more detailed information about the column, ta=
ble
>> or parameter that is causing the constraint violation in this scenario?=
 I
>> would greatly appreciate any guidance or advice you could provide on th=
is
>> matter

> What you see is what you get.

More to the point, you have the wrong mental model: a domain constraint
violation might not be associated with a table column at all.
For example,

postgres=3D# select (-1)::my_domain;
ERROR:  value for domain my_domain violates check constraint "value_min"

There is some useful data split out into fields of the error report:

postgres=3D# \errverbose =

ERROR:  23514: value for domain my_domain violates check constraint "value=
_min"
SCHEMA NAME:  public
DATATYPE NAME:  my_domain
CONSTRAINT NAME:  value_min
LOCATION:  ExecEvalConstraintCheck, execExprInterp.c:3651

... but the only context that's available is the domain name.

If you made the constraints be table check constraints, then
you'd have localization of the sort you want:

postgres=3D# create table t1 (col1 int check (col1 > 0));
CREATE TABLE
postgres=3D# insert into t1 values (-1);
ERROR:  new row for relation "t1" violates check constraint "t1_col1_check=
"
DETAIL:  Failing row contains (-1).
postgres=3D# \errverbose =

ERROR:  23514: new row for relation "t1" violates check constraint "t1_col=
1_check"
DETAIL:  Failing row contains (-1).
SCHEMA NAME:  public
TABLE NAME:  t1
CONSTRAINT NAME:  t1_col1_check
LOCATION:  ExecConstraints, execMain.c:2023

			regards, tom lane