Postgres handle case to continue Insert other row if some row doesn't exists

Question

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?

• Colin 't Hart: Remove the values clause and turn it into a select that returns 0 or 1 row depending on what data exists.
• jgrocha: See dba.stackexchange.com/questions/2973/…

Answer 1

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;