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