PostgreSQL; Using a SELECT sub-query in an INSERT

Question

I am trying to use a SELECT sub-query to select all staff users who have the attribute Likes tea and then for all other staff users insert into the table a row for each user with the attribute saying that they also like tea (it turns out everyone likes tea but that wasn't recorded when the table data was gathered).

This is an example of the data set;

 tbl_users:
 user | attribute     
------+---------------
 dave | Likes tea     
 dave | Likes coffee  
 alan | Likes tea     
 fred | Likes juice   
 fred | Likes water   
 john | Likes cola    

 tbl_groups:
 user | group
------+---------------
 dave | staff         
 alan | staff         
 fred | staff         
 john | staff         
 neil | non-staff     
 tony | non-staff

Below are the missing statements that would achieve the same result if I were to write out individually all the required INSERT statements, however there are thousands of INSERTs required in the real data set so I really want to use a sub-query (or similar) to do it for me:

INSERT INTO tbl_users (user, attribute) VALUES ('fred', 'Likes tea');
INSERT INTO tbl_users (user, attribute) VALUES ('john', 'Likes tea');

This is the closest I have come to selecting all the users (not creating the required INSERTs, just selecting them to ensure I have the correct user list):

SELECT DISTINCT(tu.username) FROM tbl_users tu LEFT JOIN 

    (
     SELECT u.username FROM tbl_users u INNER JOIN tbl_groups g
     ON (u.username=g.username)
     WHERE g.groupname = 'staff' and u.attribute = 'Likes tea'
     ) AS ts

ON tu.username=ts.username
WHERE tu.username!=ts.username ORDER BY tu.username;

The problem is that this is returning every user listed in tbl_users. I think the problem is with the LEFT JOIN to the sub-query, however running that sub-query on its own sucessfully returns only the list of users who like tea, so I was expecting this part to then select the inverse user set from tbl_users (all users without the 'Likes tea' attribute) WHERE tu.username!=ts.username ORDER BY tu.username;

What's going on here, is the ON clause overiding the WHERE clause?

EDIT:

This is a pseudo example of what I want to do:

The sub-query is selecting all the users in my specified group (staff) with the attrbiute already defined, so I need to use that user list in some sort of NOT IN fassion against the entire users list:

for each (username from list of all users)
  if username NOT IN (sub-query of users who already have the attribute)
  then
    add attribute to this user
  fi
next

Laurenz Albe · Accepted Answer · 2016-06-21 09:09:41Z

5

You want to insert all users that are 'staff', except the ones that already 'Likes tea', right?

Translate that into SQL, and you get:

INSERT INTO tbl_users (username, attribute)
   SELECT username, VARCHAR 'Likes tea' AS attribute
      FROM tbl_groups
      WHERE groupname = 'staff'
   EXCEPT SELECT username, 'Likes tea'
      FROM tbl_users
      WHERE attribute = 'Likes tea';

answered Jun 21, 2016 at 9:09

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Baldrick Over a year ago

I did not know about this EXCEPT SELECT... clause, super! Thanks :)

Collectives™ on Stack Overflow

PostgreSQL; Using a SELECT sub-query in an INSERT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related