INSERT multiple values via join

I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys.

CREATE TABLE Person (
person_id SERIAL PRIMARY KEY,
username VARCHAR NOT NULL UNIQUE
);

CREATE TABLE Group (
group_id SERIAL PRIMARY KEY,
group_name VARCHAR NOT NULL UNIQUE
);

CREATE TABLE GroupMember (
person_id INTEGER REFERENCES Person,
group_id INTEGER REFERENCES Group,
PRIMARY KEY (person_id, group_id)
);

I would like to insert multiple entries into the relationship relation GroupMember, but using the natural keys of the associated entities. Something along the lines of:

INSERT INTO GroupMember (person_id,group_id)
SELECT person_id,group_id
FROM Person P, Group G, (
('alice','girls'),
('bob','boys'),
('alice','coolkids'),
('bob','coolkids')
) AS X
WHERE P.username = X.1 AND G.group_name = X.2;

Obviously the above syntax isn't valid, but hopefully conveys the idea. Is there a way to do this in PostgreSQL?

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs.

INSERT INTO groupmember (person_id, group_id)
SELECT p.person_id, g.group_id
FROM  (
   VALUES
     ('alice'::varchar, 'girls'::varchar)
   , ('bob','boys',
   , ('alice','coolkids')
   , ('bob','coolkids')
  ) x (username, group_name)
JOIN   person p  USING (username)
JOIN   "group" g USING (group_name);

Aside: don't use the reserved word group as table name.

StackExchange Database Administrators Q#91937, answer score: 13