1

I am new to database triggers/PostgreSQL and trying to convert the following SQL trigger to PostgreSQL.

SQL script : 

CREATE TRIGGER tr_EmpMerger ON Emp INSTEAD OF INSERT
AS
BEGIN
    MERGE INTO Emp AS Target
    USING ( SELECT * FROM INSERTED ) AS Source
    ON
    (   Target.EmpId = Source.EmpId
    )
    WHEN MATCHED THEN UPDATE SET
        EmpName = Source.EmpName,
        Age  =  Source.Age
    WHEN NOT MATCHED THEN INSERT VALUES 
    (
        Source.EmpId,
        Source.EmpName,
        Source.Age        
    );
END
GO

Questions :

1) Is there any equivalent of SQL's INSERTED table in PostgreSQL? If not, what is the work around?

2) Does PostgreSQL support Merge triggers? If not, what is the work around?

3) What will be the equivalent PostgreSQL script for the above merge trigger?

EDIT :

Note - In this scenario the insertion of data into the Emp table (as well as other tables) is happening through Bulk Copy command of Postgres. So there is no direct INSERT INTO query available for this table

code_digger
  • 11
  • 2
  • In Postgres, you would implement this using an `on conflict` clause on the `insert` statement. – Gordon Linoff Nov 19 '19 at 12:42
  • 1) there is something similar when using statement level triggers - it's called ["transation table"](https://www.postgresql.org/docs/current/sql-createtrigger.html) . In most cases you would use a row level trigger where you don't need that. 2) Postgres' equivalent to MERGE is `INSERT ON CONFLICT` 3) you don't really need a trigger for that in Postgres, just use INSERT ON CONFLICT directly. –  Nov 19 '19 at 13:36
  • INSTEAD OF triggers can only be defined on views in Postgres, not tables. –  Nov 19 '19 at 13:36
  • Thank you for the prompt response! In this scenario the insertion of data into the tables is happening through Postgres Bulk Copy. Is it possible to add the functionality of INSERT ON CONFLICT to the Bulk Copy way of data insertion into Postgres tables? – code_digger Nov 26 '19 at 06:57

0 Answers0