insert missing user records from a union in postgresql

Question

I have three tables (users, students, and staff). All three tables share three common attributes (emplid, first_name, and last_name). I want to be able to do a union on students and staff like so:

SELECT emplid, first_name, last_name 
FROM students 
UNION 
SELECT emplid, first_name, last_name 
FROM staff

I then want to take that resulting query and insert any users into the users table that are considered to be "new" (in the unioned results but not in the query table).

How would I accomplish this using SQL (specifically PostgreSQL)?

INSERT INTO users (...)?

The question is: why do you have three identical tables in the first place? You should only have a single table and another table defining the "role" of that person (student, staff, user) — , Sep 11 '14 at 21:08
@a_horse_with_no_name well this example is GREATLY over-simplified. The tables contain lots of other information that makes them unique. These are the only three columns they share between them — Kyle Decot, Sep 11 '14 at 22:52
@kyledecot Still sounds like a relational modelling problem. Your `students` and `staff` tables should both have a foreign key on `emp_id` referring to `users(emp_id)`. — Craig Ringer, Sep 12 '14 at 01:40
@CraigRinger Unfortunately I don't have control over either of those tables at my company so I have to work with what I'm given. Unfortunate but that's the way big companies go sometimes :p — Kyle Decot, Sep 12 '14 at 03:05

score 1 · Accepted Answer · edited May 23 '17 at 12:21

LEFT JOIN / IS NULL would probably simplest here:

INSERT INTO users (emplid, first_name, last_name)
SELECT sub.emplid, sub.first_name, sub.last_name
FROM (
   SELECT emplid, first_name, last_name
   FROM   students
   UNION
   SELECT emplid, first_name, last_name
   FROM   staff
   ) sub
LEFT JOIN users u2 USING (emplid, first_name, last_name)
WHERE u2.emplid IS NULL;

You can join LEFT JOIN to another instance of the same users table before the UPDATE.

And I hope you are doing this to clean up all the redundant storage? (Probably no use saving all those columns in multiple tables.)

Alternatively, you could eliminate existing rows in users from each leg of the UNION query before the UNION. More verbose, but might be cheaper with complete index support, because eliminating duplicates with UNION gets cheaper.

Overview over related techniques:

Select rows which are not present in other table

... and remember to `LOCK TABLE staff, students, users IN EXCLUSIVE MODE` first. — Craig Ringer, Sep 12 '14 at 01:40

insert missing user records from a union in postgresql

1 Answers1