Re: PostgreSQL CHECK Constraint
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
Re: PostgreSQL CHECK Constraint
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 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
>
Re: PostgreSQL CHECK Constraint
> 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
Re: PostgreSQL CHECK Constraint
Hi, Kirsten, That sounds brilliant. Are there any examples on the web these days? Regards, David On Sunday, 3 October 2021, Karsten Hilbert 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 > > >
Re: PostgreSQL CHECK Constraint
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
Re: PostgreSQL CHECK Constraint
Hi， Christian, That is interesting. Can errors be captured and saved as data with scripting? Regards, David On Sunday, 3 October 2021, Christian Ramseyer 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 > >
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@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
