Re: AFTER INSERT trigger INSERT into another table-B are ignoring Table-B constraints

[M Sarwar <sarwarmd02@xxxxxxxxxxx>]

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@xxxxxx <wolters.k@xxxxxx>
Sent: Wednesday, May 22, 2024 6:38 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxx>
Datum: 22. Mai 2024
An: "pgsql-admin@xxxxxxxxxxxxxxxxxxxx" <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
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