populating a link table in a script

so i have a script like this:

CREATE TABLE person
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(80)
)

CREATE TABLE nationality
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(80)
)

INSERT INTO person (name) VALUES ('John');
INSERT INTO person (name) VALUES ('Fred');
INSERT INTO person (name) VALUES ('Mary');
INSERT INTO person (name) VALUES ('Jane');

INSERT INTO nationality (name) VALUES ('Canadian');
INSERT INTO nationality (name) VALUES ('American');

CREATE TABLE person_nationality
(
    id SERIAL PRIMARY KEY,
    person_id INTEGER,
    nationality_id INTEGER
)

The final table is a link table to link people to nationalities. Is there a way to auto populate this? like I know the first three should be linked to Canadian and the final one should be American. But I dont know how to make a script to do this.

CodePudding user response：

Use insert:

with people (person_name, nationality_name) as (
  values ('John', 'Canadian'), ('Fred', 'Canadian'), 
         ('Mary', 'Canadian'), ('Jane', 'American')
)
insert into person_nationality (person_id, nationality_id)
select p.id as person_id, n.id as nationality_id
  from people i
       join person p on p.name = i.person_name
       join nationality n on n.name = i.nationality_name;

Fiddle Example