agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to capture error message and save to a table in PostgreSQL?
4+ messages / 4 participants
[nested] [flat]
* How to capture error message and save to a table in PostgreSQL?
@ 2021-10-08 09:45 Shaozhong SHI <shishaozhong@gmail.com>
2021-10-08 17:09 ` Re: How to capture error message and save to a table in PostgreSQL? Steve Midgley <science@misuse.org>
2021-10-09 18:22 ` Re: How to capture error message and save to a table in PostgreSQL? Karsten Hilbert <Karsten.Hilbert@gmx.net>
0 siblings, 2 replies; 4+ messages in thread
From: Shaozhong SHI @ 2021-10-08 09:45 UTC (permalink / raw)
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to capture error message and save to a table in PostgreSQL?
2021-10-08 09:45 How to capture error message and save to a table in PostgreSQL? Shaozhong SHI <shishaozhong@gmail.com>
@ 2021-10-08 17:09 ` Steve Midgley <science@misuse.org>
1 sibling, 0 replies; 4+ messages in thread
From: Steve Midgley @ 2021-10-08 17:09 UTC (permalink / raw)
To: Shaozhong SHI <shishaozhong@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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?
https://www.postgresql.org/docs/14/plpgsql-trigger.html
https://www.postgresql.org/docs/14/event-trigger-example.html
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to capture error message and save to a table in PostgreSQL?
2021-10-08 09:45 How to capture error message and save to a table in PostgreSQL? Shaozhong SHI <shishaozhong@gmail.com>
@ 2021-10-09 18:22 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2021-10-09 18:29 ` Re: How to capture error message and save to a table in PostgreSQL? Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Karsten Hilbert @ 2021-10-09 18:22 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to capture error message and save to a table in PostgreSQL?
2021-10-08 09:45 How to capture error message and save to a table in PostgreSQL? Shaozhong SHI <shishaozhong@gmail.com>
2021-10-09 18:22 ` Re: How to capture error message and save to a table in PostgreSQL? Karsten Hilbert <Karsten.Hilbert@gmx.net>
@ 2021-10-09 18:29 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rob Sargent @ 2021-10-09 18:29 UTC (permalink / raw)
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>; +Cc: pgsql-sql@lists.postgresql.org
> 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 way to the server with bad data.
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-10-09 18:29 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-08 09:45 How to capture error message and save to a table in PostgreSQL? Shaozhong SHI <shishaozhong@gmail.com>
2021-10-08 17:09 ` Steve Midgley <science@misuse.org>
2021-10-09 18:22 ` Karsten Hilbert <Karsten.Hilbert@gmx.net>
2021-10-09 18:29 ` Rob Sargent <robjsargent@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