5

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?

1 Answer 1

13

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.