Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to capture error message and save to a table in PostgreSQL?

How to capture error message and save to a table in PostgreSQL?

From
Shaozhong SHI
Date:
Hi, All, 

I wish to test out to produce a table when data violates constraints when insert into a new table with constraint set.  How to capture and save error message to a new table?

INSERT INTO test3 ("Record Type", "Unique Reference Number", "Supplier Reference Number", "Post Code")
SELECT "Record Type", "Unique Reference Number", "Supplier Reference Number", "Post Code" from test;

ERROR:  new row for relation "test3" violates check constraint "test3_Post Code_check"
DETAIL:  Failing row contains (L, 14986526, 1207174, null, null, null, null, null, null, SURREY, null, null, null, null, null, null, null, null).
SQL state: 23514

Regards,
David

Re: How to capture error message and save to a table in PostgreSQL?

From
Steve Midgley
Date:


On Fri, Oct 8, 2021 at 2:46 AM Shaozhong SHI <shishaozhong@gmail.com> wrote:
Hi, All, 

I wish to test out to produce a table when data violates constraints when insert into a new table with constraint set.  How to capture and save error message to a new table?

INSERT INTO test3 ("Record Type", "Unique Reference Number", "Supplier Reference Number", "Post Code")
SELECT "Record Type", "Unique Reference Number", "Supplier Reference Number", "Post Code" from test;

ERROR:  new row for relation "test3" violates check constraint "test3_Post Code_check"
DETAIL:  Failing row contains (L, 14986526, 1207174, null, null, null, null, null, null, SURREY, null, null, null, null, null, null, null, null).
SQL state: 23514


Maybe create a trigger and trigger on error? I've never done it but it looks like it is doable.. Does this help?

Re: How to capture error message and save to a table in PostgreSQL?

From
Karsten Hilbert
Date:
Am Fri, Oct 08, 2021 at 10:45:45AM +0100 schrieb Shaozhong SHI:

> I wish to test out to produce a table when data violates constraints when
> insert into a new table with constraint set.  How to capture and save error
> message to a new table?

That depends on how you access the table and at which level
you wish the above to happen.

You need to be less generic to get better help.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: How to capture error message and save to a table in PostgreSQL?

From
Rob Sargent
Date:

> On Oct 9, 2021, at 12:22 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
> Am Fri, Oct 08, 2021 at 10:45:45AM +0100 schrieb Shaozhong SHI:
>
>> I wish to test out to produce a table when data violates constraints when
>> insert into a new table with constraint set.  How to capture and save error
>> message to a new table?
>
> That depends on how you access the table and at which level
> you wish the above to happen.
>
> You need to be less generic to get better help.
>
If there is a client application involved you might be better off validating the input there rather than going all the
wayto the server with bad data.  


>