1

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?

1 Answer 1

0

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'))

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.