Обсуждение: How to capture error message and save to a table in PostgreSQL?
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
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?
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
> 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. >