postgresql - postgres: 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 exists.

question from:https://stackoverflow.com/questions/66045636/postgres-insert-multiple-rows-into-a-table-with-id-from-other-table-if-not-exis

Answer 1

Something like this might work for you:

Insert into feedbacks (username) 
select distinct uname from 
    (values 
        ('sample_data'),
        ('sample_data2'),
        ('sample_data3')
    ) s(uname) 
where not exists(select 1 from feedbacks where username=uname);