agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Christian Ramseyer <ramseyer@netnea.com>
To: Shaozhong SHI <shishaozhong@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: PostgreSQL CHECK Constraint
Date: Sun, 3 Oct 2021 16:01:57 +0200
Message-ID: <42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com> (raw)
In-Reply-To: <CA+i5Jwbo7QL+H4Q1tFV1CUQaa4ZzTLZFVwEC-YKe-bDVRsOBMA@mail.gmail.com>
References: <CA+i5JwYC26KFQjXYBk-gNYxofhkjnWQBB-+goeusMajVdv_2_w@mail.gmail.com>
<CA+i5Jwbo7QL+H4Q1tFV1CUQaa4ZzTLZFVwEC-YKe-bDVRsOBMA@mail.gmail.com>
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
view thread (9+ messages) latest in thread
Message-ID: <42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com>
Permalink: ../42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com/
Also on: postgresql.org/message-id/42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ramseyer@netnea.com, shishaozhong@gmail.com, pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL CHECK Constraint
In-Reply-To: <42aaef2a-6f55-0449-5a35-d6507d333af1@netnea.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox