0

I am trying to insert data to a table using below query:

INSERT INTO projects_users (user_id, project_id, is_admin) VALUES (
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        ),(
            (Select id from users where username='example'),
            (Select id from projects where name='example'),
            True
        )

This query fails when some user is not there in users table (Select id from users where username='does_not_exists').

I want to handle such cases, and skip such rows (where username doesn't exists) and continue adding other rows.

In case of duplicate rows, I know we can do "On Conflict Do Nothing". Is there something similar available for cases like this? And is it a good practice to do?

2
0

Build a SELECT expression that does what you want, and then use INSERT INTO ... SELECT ... to insert it into the table.

To get a SELECT that does what you want, wrap the VALUES expression in parentheses, add an alias with field names for your sub-selects, then add a WHERE for the project and user IDs being NOT NULL. The final query becomes:

insert into projects_users select * from 
(VALUES (
            (Select id from users where username='user_id1'),
            (Select id from projects where name='project_id2'),
            True
        ),(
            (Select id from users where username='user_id3'),
            (Select id from projects where name='project_id4'),
            True
        )
) as r(user_id, project_id, is_admin)
WHERE user_id is not null and project_id is not null;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.