databasepostgresqlfunctiontriggers

How can I write a trigger and function to insert data into table2 when a new row is inserted into table1, while avoiding duplicates in table2?


I want to create a trigger and function that ensures when I insert a row into table1, a new row is inserted into table2.
However, the function must not insert a row into table2 if a row with the same field/value already exists in table2.

Example:
I have two tables: table1 and table2
When I perform 3 separate inserts into table1:

The resulting data must be:

table1: 
| name   | country |
|--------|---------|
| Sam    | USA     |
| Jean   | USA     |
| Kath   | Canada  |

table2:
| country |
|---------|
| USA     |
| Canada  |

I am aware that I could find unique values of country using just table1 But I need a separate table\ Also solution has to be for postgres


Solution

  • Following trigger and function could be used:

    CREATE FUNCTION insert_into_table2() RETURNS trigger AS
    $$
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM table2 WHERE country = NEW.country) THEN
            INSERT INTO table2 (country) VALUES (NEW.country);
        END IF;
        RETURN NEW;
    END;
    $$
        LANGUAGE plpgsql;
    
    CREATE TRIGGER trigger_insert_into_table2
        AFTER INSERT
        ON table1
        FOR EACH ROW
    EXECUTE FUNCTION insert_into_table2();
    

    In order to ensure that there is no duplicates table2.country, the field has to be marked as UNIQUE

    CREATE TABLE table2
    (
        country VARCHAR(50) UNIQUE
    );