Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint - Mailing list pgsql-novice

"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, 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

> 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=# 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=# \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=# create table t1 (col1 int check (col1 > 0));
CREATE TABLE
postgres=# insert into t1 values (-1);
ERROR:  new row for relation "t1" violates check constraint "t1_col1_check"
DETAIL:  Failing row contains (-1).
postgres=# \errverbose
ERROR:  23514: new row for relation "t1" violates check constraint "t1_col1_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