How to rebuild this query using joins

help me please to understand, how to remake this query using joins.

INSERT INTO mytable
    (user_id, tag)
SELECT ?, ?
WHERE NOT EXISTS (SELECT user_id FROM mytable WHERE user_id=? AND tag=?)

I saw similar questions that use two different tables, but here i have one table. I need to insert only if the same entry does not exist. mytable table does not have any UNIQUE constraints and i can't change scheme.

Solution 1:

You can put your parameters into a common table expression that is reusable:

with data (user_id, tag) as (
  values (?,?)
)
INSERT INTO mytable (user_id, tag)
SELECT d.user_id, d.tag
FROM data d
WHERE NOT EXISTS (SELECT *
                  FROM mytable t
                    cross join data d
                  WHERE t.user_id = d.user_id 
                    AND t.tag = d.tag)

Note that this will not prevent concurrent insert of the same values. The only way to achieve that is to add a unique constraint (and then you can use on conflict do nothing)