Subject: Re: PostgreSQL CHECK Constraint
To: Shaozhong SHI <shishaozhong@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: 
 <CA+i5JwYC26KFQjXYBk-gNYxofhkjnWQBB-+goeusMajVdv_2_w@mail.gmail.com>
 <CA+i5Jwbo7QL+H4Q1tFV1CUQaa4ZzTLZFVwEC-YKe-bDVRsOBMA@mail.gmail.com>
From: Christian Ramseyer <ramseyer@netnea.com>
Message-ID: <42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com>
Date: Sun, 3 Oct 2021 16:01:57 +0200
MIME-Version: 1.0
In-Reply-To: 
 <CA+i5Jwbo7QL+H4Q1tFV1CUQaa4ZzTLZFVwEC-YKe-bDVRsOBMA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/42aaef2a-6f55-0449-5a35-d6507d333af1%40netnea.com>
Precedence: bulk


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@dellstore ERROR:  new row for relation "test_customers"
violates check constraint "check_age"

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

postgres@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