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

On Fri, Oct 8, 2021 at 2:46 AM Shaozhong SHI <shishaozhong(at)gmail(dot)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?

https://www.postgresql.org/docs/14/plpgsql-trigger.html
https://www.postgresql.org/docs/14/event-trigger-example.html