Re: PostgreSQL CHECK Constraint

On 03.10.21 09:31, Shaozhong SHI wrote:
>
> Has anyone got experience with data quality checking, validation and
> reporting within PostgreSQL?
>
> How best to use PostgreSQL CHECK Constraint for data quality checking,
> validation and reporting?  
>
> Can we report on errors in a detailed and specific way?  For instance,
> can we produce report on specific issues of erroneous cells in which row
> and etc.?
>

Yes that's all possible. Given a constraint like

alter table test_customers
add constraint check_age check (age >= 18);

The reported error looks like this:

postgres(at)dellstore ERROR: new row for relation "test_customers"
violates check constraint "check_age"

postgres(at)dellstore DETAIL: Failing row contains (1, Jimmy, Schmoe, 15).

postgres(at)dellstore STATEMENT: insert into test_customers (firstname,
lastname, age) values ( 'Jimmy', 'Schmoe', 15);

This errors appears in the serverlog which has many format and
forwarding options, you can read about them here:

https://www.postgresql.org/docs/current/runtime-config-logging.html

Cheers
Christian

--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com