Am 12.11.20 um 10:00 schrieb Il Mimo di Creta:
Hello Everyone,I am writing to ask help about a use case I have to set up: if anyone can provide me with any suggestions, I would be really grateful.This is my use case:I have a table, with a primary key composed of two columns, each one, of course, with a not null constraint. Each night a bunch of data will be loaded from an external ETL, which is out of my control. Since I know there might be data quality issues, such as duplicated records or invalid records with null values on not-null columns, I would like to save such records, and only such records, in an auxiliary table.Currently, I tried two solutions:1) Trigger on insert: but I could not have the insert into the auxiliary table working, because the constraints violation triggers a rollback 2) Rule on insert, with a "DO INSTEAD" condition, which works, but, in case of invalid records, copies all the row of the transaction in the auxiliary table and not only the invalid ones.This is what I did:Let's assume that my table is actually name "mytable", with pk composed of (col1,col2).The auxiliary table is mytable_failures, which has the same columns of mytable, no constraints and an additional column "fail_reason".These are the rules: CREATE or REPLACE RULE insert_mytable_nulls AS ON INSERT TO mytable where (length(trim(NEW.col1))=0 OR length(trim(NEW.col2))=0 OR NEW.col1 IS NULL OR NEW.col2 IS NULL) DO INSTEAD insert into mytable_failures values(NEW.*,'col1 and col2 cannot be null'); CREATE or REPLACE RULE insert_mytable_pkey AS ON INSERT TO mytable where (select true from mytable where col1=NEW.col1 and col2=NEW.col2) DO INSTEAD insert into mytable_failures values(NEW.*,'Primary Key violation'); If I execute the following transaction batch: INSERT INTO mytable( col1, col2, col3, col3, last_update) VALUES ('UK', 'FB00004', 'en', now()); INSERT INTO mytable( col1, col2, col3, col3, last_update) VALUES ('UK', 'FB00005', 'en', now()); INSERT INTO mytable( col1, col2, col3, col3, last_update) VALUES ('UK', 'FB00004', 'en', now());1) the first two rows are correctly inserted in mytable and the third discarded2) incorrectly, all the three rows in the mytable_failures. Thank you for all the help you can provide Mimo
I think this might help: https://www.postgresqltutorial.com/postgresql-upsert/
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachment:
OpenPGP_0x8A579C93B31961BA.asc
Description: application/pgp-keys
Attachment:
OpenPGP_signature
Description: OpenPGP digital signature
