agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedPostgreSQL CHECK Constraint
9+ messages / 3 participants
[nested] [flat]
* PostgreSQL CHECK Constraint
@ 2021-10-03 07:30 Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shaozhong SHI @ 2021-10-03 07:30 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.?
Regards,
David
^ permalink raw reply [nested|flat] 9+ messages in thread
* PostgreSQL CHECK Constraint
@ 2021-10-03 07:31 Shaozhong SHI <shishaozhong@gmail.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Shaozhong SHI @ 2021-10-03 07:31 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
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.?
Regards,
David
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 14:01 Christian Ramseyer <ramseyer@netnea.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Christian Ramseyer @ 2021-10-03 14:01 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; pgsql-general <pgsql-general@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:16 Shaozhong SHI <shishaozhong@gmail.com>
parent: Christian Ramseyer <ramseyer@netnea.com>
0 siblings, 2 replies; 9+ messages in thread
From: Shaozhong SHI @ 2021-10-03 18:16 UTC (permalink / raw)
To: Christian Ramseyer <ramseyer@netnea.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Hi， Christian,
That is interesting. Can errors be captured and saved as data with
scripting?
Regards,
David
On Sunday, 3 October 2021, Christian Ramseyer <ramseyer@netnea.com> wrote:
>
>
> 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
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:35 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Karsten Hilbert @ 2021-10-03 18:35 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI:
> That is interesting. Can errors be captured and saved as data with
> scripting?
Depends on what the script does.
If the script runs (or is written in) Python the canonical PG
driver (psycopg2/3) will give you such data.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:44 Shaozhong SHI <shishaozhong@gmail.com>
parent: Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Shaozhong SHI @ 2021-10-03 18:44 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Hi, Kirsten,
That sounds brilliant.
Are there any examples on the web these days?
Regards,
David
On Sunday, 3 October 2021, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
> Am Sun, Oct 03, 2021 at 07:16:32PM +0100 schrieb Shaozhong SHI:
>
> > That is interesting. Can errors be captured and saved as data with
> > scripting?
>
> Depends on what the script does.
>
> If the script runs (or is written in) Python the canonical PG
> driver (psycopg2/3) will give you such data.
>
> Karsten
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:47 Christian Ramseyer <ramseyer@netnea.com>
parent: Shaozhong SHI <shishaozhong@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Christian Ramseyer @ 2021-10-03 18:47 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
> The reported error looks like this:
>
> postgres@dellstore ERROR: new row for relation "test_customers"
> violates check constraint "check_age"
..
>
> This errors appears in the serverlog which has many format and
> forwarding options, you can read about them here:
On 03.10.21 20:16, Shaozhong SHI wrote:
> Hi， Christian,
> That is interesting. Can errors be captured and saved as data with
> scripting?
Yes that works quite the same, e.g. in Python you can do
try:
cur.execute("insert into test_customers (firstname, lastname, age)
values ( %s, %s, %s)", ("Bobby", "Tables", 10))
except psycopg2.errors.CheckViolation as e:
print(f"That didn't work: {e.cursor.query} failed")
print(f"{e.pgerror}")
HTH
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:53 Shaozhong SHI <shishaozhong@gmail.com>
parent: Christian Ramseyer <ramseyer@netnea.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shaozhong SHI @ 2021-10-03 18:53 UTC (permalink / raw)
To: Christian Ramseyer <ramseyer@netnea.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Hi, Christian,
Brilliant!
Some years ago, I did something similar and saved problematic ones for data
collector.
Now, I am reviewing to see whether there be any elegant way to automate
reporting and giving feedback.
Regards,
David
On Sunday, 3 October 2021, Christian Ramseyer <ramseyer@netnea.com> wrote:
> > The reported error looks like this:
> >
> > postgres@dellstore ERROR: new row for relation "test_customers"
> > violates check constraint "check_age"
> ..
> >
> > This errors appears in the serverlog which has many format and
> > forwarding options, you can read about them here:
>
> On 03.10.21 20:16, Shaozhong SHI wrote:
> > Hi， Christian,
> > That is interesting. Can errors be captured and saved as data with
> > scripting?
>
> Yes that works quite the same, e.g. in Python you can do
>
> try:
> cur.execute("insert into test_customers (firstname, lastname, age)
> values ( %s, %s, %s)", ("Bobby", "Tables", 10))
> except psycopg2.errors.CheckViolation as e:
> print(f"That didn't work: {e.cursor.query} failed")
> print(f"{e.pgerror}")
>
>
> HTH
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PostgreSQL CHECK Constraint
@ 2021-10-03 18:56 Karsten Hilbert <Karsten.Hilbert@gmx.net>
parent: Shaozhong SHI <shishaozhong@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Karsten Hilbert @ 2021-10-03 18:56 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
--
Karsten
Sure, they are just a search engine's use away.
> Are there any examples on the web these days?
> That sounds brilliant.
:Am Sun, Oct 03, 2021 at 07:44:41PM +0100 schrieb Shaozhong SHI
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2021-10-03 18:56 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-03 07:30 PostgreSQL CHECK Constraint Shaozhong SHI <shishaozhong@gmail.com>
2021-10-03 07:31 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-03 14:01 ` Christian Ramseyer <ramseyer@netnea.com>
2021-10-03 18:16 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-03 18:35 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2021-10-03 18:44 ` Shaozhong SHI <shishaozhong@gmail.com>
2021-10-03 18:56 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2021-10-03 18:47 ` Christian Ramseyer <ramseyer@netnea.com>
2021-10-03 18:53 ` Shaozhong SHI <shishaozhong@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox