pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
8+ messages / 4 participants
[nested] [flat]
* AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-22 21:14 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 8+ messages in thread
From: M Sarwar @ 2024-05-22 21:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hello All,
This is a trigger related question.
Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.
CREATE TRIGGER tr_test_part_details_all_mcm_init
AFTER INSERT
ON bx.test_part_details_all_mcm_init
FOR EACH ROW
EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();
Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_mid
Table, bx.test_part_details_all_mcm_mid has a constraint,
CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, part_pf)
When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing the data in bx.test_part_details_all_mcm_mid. I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.
What is that I am missing here?
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-22 22:38 wolters.k@web.de
0 siblings, 1 reply; 8+ messages in thread
From: wolters.k@web.de @ 2024-05-22 22:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
<html style="background-color:transparent"><head><meta charset="utf-8" name="viewport" content="width=device-width">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body contenteditable="false" style="margin-left:10px;">
<div id="8787634986986720895ty" contenteditable="false">
<div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Hi,</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">getting trigger and functions over several tables smooth at work is sometimes difficult, as there are many points for unecpexted stuff. Maybe some of the following hints are helpful? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">maybe you can check the unique constraint at first with a simple insert of several rows in your table <span style="font-family: Aptos, serif; font-size: 10.666667px;">bx.test_part_details_all_mcm_</span><b style="font-family: Aptos, serif; font-size: 10.666667px;">mid</b> if everything works as expected. </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Does your constraint <span style="font-family: Aptos, serif; font-size: 10.666667px;">cons_unique_for_concatenated_view</span> include all columns of the table (the comparision works for the whole table or a few columns)? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Are there any further update- /insert-, etc. trigger or functions that operate at the some table ( <span style="font-family: Aptos, serif; font-size: 10.666667px;">bx.test_part_details_all_mcm_</span><b style="font-family: Aptos, serif; font-size: 10.666667px;">mid</b>) - in worst case at the same time? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Are the duplicated rows containg NULL- values in a column? </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">„<span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: "Open Sans", sans-serif; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(33, 33, 33);"> By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns“ (</span><a href="https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS"... id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Then you could solve the Problem by adding <span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: monospace, monospace; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(74, 74, 74);">NULLS NOT DISTINCT</span> (compare further down same link)</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Good luck! </div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">kind regards,</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;">Katrin</div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><span style="caret-color: rgb(243, 245, 249); color: rgb(243, 245, 249); font-family: monospace, monospace; font-size: 14.4px; -webkit-tap-highlight-color: rgba(0, 0, 0, 0); -webkit-text-size-adjust: 100%; background-color: rgb(74, 74, 74);"></span></div><div id="338317EditableDivID--xx--yy--42" style="font-family: Verdana;font-size:17px; color:black;"><br>Am 22.05.24 um 23:15 schrieb M Sarwar
<br><br>
</div>
<div style="border-left:2px solid gray;padding-left:10px;width:100%%">
<div style="font-family: Verdana;font-size:17px; color:black;">
Von: "M Sarwar" <sarwarmd02@outlook.com><br>Datum: 22. Mai 2024<br>An: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org><br>Cc: <br>Betreff: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
</div>
<div class="elementToProof" style="font-family: Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, serif; font-size: 8pt; color: rgb(0, 0, 0);">
Hello All,</div>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">This is a trigger related question.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">CREATE TRIGGER tr_test_part_details_all_mcm_init</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> AFTER INSERT</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> ON bx.test_part_details_all_mcm_<b>init</b></span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> FOR EACH ROW</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_<b>mid</b></span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">Table, bx.test_part_details_all_mcm_<b>mid
</b>has a constraint,</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id,
serial_number, part_pf)</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;"> </span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing
the data in bx.test_part_details_all_mcm_<b>mid. </b>I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"><span style="font-size: 8pt;">What is that I am missing here?</span></p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;">Thanks,</p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;">Sarwar</p>
<p style="margin: 0in; font-family: Aptos, serif; font-size: 11pt;"> </p>
</div>
</div>
</body></html>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 00:29 M Sarwar <sarwarmd02@outlook.com>
parent: wolters.k@web.de
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2024-05-23 00:29 UTC (permalink / raw)
To: wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Kartin,
I truly appreciate your response.
I got the below error message when I tried to add the constraint.
ALTER TABLE bronx.test_part_details_all_mcm_mid
ADD CONSTRAINT cons_unique_for_concatenated_view
UNIQUE NULLS NOT DISTINCT (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, temperature, part_pf);
ERROR: syntax error at or near "NULLS"
LINE 3: UNIQUE NULLS NOT DISTINCT (start_time_numeric, stop_time_... ^
SQL state: 42601 Character: 110
My Postgress version is 13.5. I hope that it is not due to the version.
Thanks,
Sarwar
________________________________
From: wolters.k@web.de <wolters.k@web.de>
Sent: Wednesday, May 22, 2024 6:38 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Hi,
getting trigger and functions over several tables smooth at work is sometimes difficult, as there are many points for unecpexted stuff. Maybe some of the following hints are helpful?
maybe you can check the unique constraint at first with a simple insert of several rows in your table bx.test_part_details_all_mcm_mid if everything works as expected.
Does your constraint cons_unique_for_concatenated_view include all columns of the table (the comparision works for the whole table or a few columns)?
Are there any further update- /insert-, etc. trigger or functions that operate at the some table ( bx.test_part_details_all_mcm_mid) - in worst case at the same time?
Are the duplicated rows containg NULL- values in a column?
„ By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns“ (https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS)
Then you could solve the Problem by adding NULLS NOT DISTINCT (compare further down same link)
Good luck!
kind regards,
Katrin
Am 22.05.24 um 23:15 schrieb M Sarwar
Von: "M Sarwar" <sarwarmd02@outlook.com>
Datum: 22. Mai 2024
An: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Cc:
Betreff: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
Hello All,
This is a trigger related question.
Table, test_part_details_all_mcm_init has a trigger, tr_test_part_details_all_mcm_init.
CREATE TRIGGER tr_test_part_details_all_mcm_init
AFTER INSERT
ON bx.test_part_details_all_mcm_init
FOR EACH ROW
EXECUTE FUNCTION bx.tr_fn_test_part_details_all_mcm_init();
Function, bx.tr_fn_test_part_details_all_mcm_init() INSERTs a row into another table, bx.test_part_details_all_mcm_mid
Table, bx.test_part_details_all_mcm_mid has a constraint,
CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, part_pf)
When I INSERT a data into the table Table, test_part_details_all_mcm_init , trigger is not checking CONSTRAINT cons_unique_for_concatenated_view while INSERTing the data in bx.test_part_details_all_mcm_mid. I am expecting that anything violating the constraint cons_unique_for_concatenated_view will throw an error. Instead of that it is allowing to INSERT the data.
What is that I am missing here?
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 00:35 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-05-23 00:35 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: wolters.k@web.de; pgsql-admin@lists.postgresql.org
On Wed, May 22, 2024, 18:29 M Sarwar <sarwarmd02@outlook.com> wrote:
>
>
> My Postgress version is 13.5. I hope that it is not due to the version.
>
Check the current docs, confirm the syntax is documented there, then look
at the v13 docs and see whether it is present or not.
David J.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 01:22 M Sarwar <sarwarmd02@outlook.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2024-05-23 01:22 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi David,
I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.
My team will not upgrade from 13 due to number of reasons.
Thank you,
Sarwar
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Wednesday, May 22, 2024 8:35 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
On Wed, May 22, 2024, 18:29 M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
My Postgress version is 13.5. I hope that it is not due to the version.
Check the current docs, confirm the syntax is documented there, then look at the v13 docs and see whether it is present or not.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 02:42 Rui DeSousa <rui.desousa@icloud.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rui DeSousa @ 2024-05-23 02:42 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On May 22, 2024, at 9:22 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Hi David,
> I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.
> My team will not upgrade from 13 due to number of reasons.
In that case you could use a functional unique index with a digest to teat nulls as not distinct instead; although I do prefer NULL to be distinct. To me this indicates that the schema is not fully normalized and/or ported from MS SQL server.
I’ve used this approach when dealing with organic schemas that had simular logic and requirements.
x_idx1 — is to fulfill queries and traditional unique values. (Better to use a constraint instead for correctness/readability)
x_idx2 — handles uniqueness when one of values is null. (Must be an index as constraints do not support functional indexes)
prod=> \d x
Table "rui.x"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"x_idx1" UNIQUE, btree (a, b, c)
"x_idx2" UNIQUE, btree (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text)) WHERE a IS NULL OR b IS NULL OR c IS NULL
prod=> insert into x values (1, 2, null);
INSERT 0 1
Time: 1.741 ms
prod=> insert into x values (1, 2, null);
ERROR: duplicate key value violates unique constraint "x_idx2"
DETAIL: Key (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text))=(\x7cce718aefddfbd5db7925f15b0ab319d7f06b4aeae096a1542f8d1adeef36be) already exists.
Time: 0.843 ms
prod=> insert into x values (2, 2, 2);
INSERT 0 1
Time: 2.451 ms
prod=> insert into x values (2, 2, 2);
ERROR: duplicate key value violates unique constraint "x_idx1"
DETAIL: Key (a, b, c)=(2, 2, 2) already exists.
Time: 0.698 ms
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 20:30 M Sarwar <sarwarmd02@outlook.com>
parent: Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2024-05-23 20:30 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Rui,
I appreciate your response on this.
I am in the scenario of using x_idx1 but with I am going thru constraints. I am not using indexes.
Constraints are working as expected when I am working the table-2 directly.
In my case, Table-2 needs to get INSERTed only when table-1 has INSERTs. Table-1 is working fine. Table-2 is also working fine when I am directly working in table-2.
But when I Table-2 needs to have INSERTs based on the trigger invocation of Table-1, Table-2 is applying constraints which is causing issues.
Thanks,
Sarwar
________________________________
From: Rui DeSousa <rui.desousa@icloud.com>
Sent: Wednesday, May 22, 2024 10:42 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>; wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
On May 22, 2024, at 9:22 PM, M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
Hi David,
I did verify at Ver 13 and this ( UNIQUE NULLS NOT DISTINCT ) syntax is not existing there. That means, I need to work on it without this option.
My team will not upgrade from 13 due to number of reasons.
In that case you could use a functional unique index with a digest to teat nulls as not distinct instead; although I do prefer NULL to be distinct. To me this indicates that the schema is not fully normalized and/or ported from MS SQL server.
I’ve used this approach when dealing with organic schemas that had simular logic and requirements.
x_idx1 — is to fulfill queries and traditional unique values. (Better to use a constraint instead for correctness/readability)
x_idx2 — handles uniqueness when one of values is null. (Must be an index as constraints do not support functional indexes)
prod=> \d x
Table "rui.x"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
c | integer | | |
Indexes:
"x_idx1" UNIQUE, btree (a, b, c)
"x_idx2" UNIQUE, btree (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text)) WHERE a IS NULL OR b IS NULL OR c IS NULL
prod=> insert into x values (1, 2, null);
INSERT 0 1
Time: 1.741 ms
prod=> insert into x values (1, 2, null);
ERROR: duplicate key value violates unique constraint "x_idx2"
DETAIL: Key (pgcrypto.digest((COALESCE(a::text, '<null>'::text) || COALESCE(b::text, '<null>'::text)) || COALESCE(c::text, '<null>'::text), 'sha256'::text))=(\x7cce718aefddfbd5db7925f15b0ab319d7f06b4aeae096a1542f8d1adeef36be) already exists.
Time: 0.843 ms
prod=> insert into x values (2, 2, 2);
INSERT 0 1
Time: 2.451 ms
prod=> insert into x values (2, 2, 2);
ERROR: duplicate key value violates unique constraint "x_idx1"
DETAIL: Key (a, b, c)=(2, 2, 2) already exists.
Time: 0.698 ms
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints
@ 2024-05-23 22:15 Rui DeSousa <rui.desousa@icloud.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rui DeSousa @ 2024-05-23 22:15 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; wolters.k@web.de <wolters.k@web.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On May 23, 2024, at 4:30 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Constraints are working as expected when I am working the table-2 directly.
> In my case, Table-2 needs to get INSERTed only when table-1 has INSERTs. Table-1 is working fine. Table-2 is also working fine when I am directly working in table-2.
> But when I Table-2 needs to have INSERTs based on the trigger invocation of Table-1, Table-2 is applying constraints which is causing issues.
CONSTRAINT cons_unique_for_concatenated_view UNIQUE (start_time_numeric, stop_time_numeric, test_action, part_type, fixture_id, run_id, auxid1_build_id, auxid2_asic_id, serial_number, part_pf)
So the constraint above is not being honored? What are the duplicate records? Is this a view that you’re seeing the duplicate records in or a table?
I was under the impression that NULLs where in play here. Is that not the case?=
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-05-23 22:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-22 21:14 AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints M Sarwar <sarwarmd02@outlook.com>
2024-05-22 22:38 Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints wolters.k@web.de
2024-05-23 00:29 ` M Sarwar <sarwarmd02@outlook.com>
2024-05-23 00:35 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-23 01:22 ` M Sarwar <sarwarmd02@outlook.com>
2024-05-23 02:42 ` Rui DeSousa <rui.desousa@icloud.com>
2024-05-23 20:30 ` M Sarwar <sarwarmd02@outlook.com>
2024-05-23 22:15 ` Rui DeSousa <rui.desousa@icloud.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox