databasepostgresqlfunctiontriggers

How to insert data into table2 when a new row is inserted into table1, while avoiding duplicates in table2?


I have two tables in PostgreSQL: table1 and table2.

When I insert a row into table1, I want a new row to be inserted into table2 only if the value of a specific field (e.g. country) does not already exist in table2.

Example:
When I perform 3 separate inserts into table1:

The resulting data must be following:

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.


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