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?

Question

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:

• ("Sam","USA")
• ("Jean", "USA")
• ("Kath", "Canada")

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

Answer 1

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
);