How to get column, table or parameter name reporting when violating DOMAIN type constraint

From: Valerio Battaglia <vabatta(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: How to get column, table or parameter name reporting when violating DOMAIN type constraint
Date: 2023-03-26 10:54:18
Message-ID: CACxJNSkpT8vC0jnsfObZDargLA1vCiP-VKF2ji9y58VaAOSpVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2023-03-26 15:13:50 Re: How to get column, table or parameter name reporting when violating DOMAIN type constraint
Previous Message Laurenz Albe 2023-03-20 05:27:44 Re: PgAdmin Lapse