Insert multiple rows into a table with id from other table if not exists insert to other table

Question

I have done similar task where I can insert a row into a table if data doesn't exists:

WITH 
 user_exists AS (
 Select id from users where username='%s'
 ),
 user_new AS (
 INSERT INTO users (username)
 SELECT w.username FROM (values ('%s')) w(username)
 WHERE not exists
 (SELECT 1 FROM users u WHERE u.username = w.username)
 returning id
 )
INSERT INTO feedbacks ('static_row', userid)
SELECT 
'static_data',
(SELECT id FROM users_exists UNION ALL SELECT id FROM users_new) AS userid

Above works well when we insert a new row to feedbacks table. If user doesn't exists it inserts data in users table and returns id which is used for inserting data to feedbacks table.

But now my use case is, I have to insert multiple rows into the feedback table. Something like this: user_variable = ['a','b', ...]

Insert into feedbacks ('static_row', userid) 
VALUES
('sample_data', (Select if from users where username='a')),
('sample_data', (Select if from users where username='b')),
('sample_data', (Select if from users where username='c')) 

For above case, how we can insert a new row to users table if username='b' doesn't exist?

Answer 1

Why not split up your separate goals into separate queries to handle them appropriately? It seems a little dangerous to be modifying data within a CTE that is then used to be read from, though not sure if this is common convention in PostgreSQL.

For example:

WITH CTE_UsersToBeAdded AS
(
 SELECT 'a' AS username
 UNION ALL
 SELECT 'b' AS username
 UNION ALL
 SELECT 'c' AS username
)
INSERT INTO users (username)
SELECT c.username
FROM CTE_UsersToBeAdded c
LEFT JOIN users u
 ON c.username = u.username
WHERE u.usernams IS NULL; -- Filters out already existing usernames
INSERT INTO feedbacks ('static_row', userid) 
VALUES
('sample_data', (Select id from users where username='a')),
('sample_data', (Select id from users where username='b')),
('sample_data', (Select id from users where username='c'))