-1

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.

Valejo
  • 11
  • 1
  • 5
  • 1
    Does this answer your question? [Postgres: INSERT if does not exist already](https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already) – esqew Jan 11 '22 at 16:41
  • @esqew Answer that you mentioned is mostly about "ON CONFLICT clause to INSERT". But table does not have UNIQUE constraint. – Valejo Jan 11 '22 at 16:52
  • 1
    Sure, that's true - I'll retract - however it's worth mentioning that whatever query trickery you need to do to get this done (a la the answer you've accepted) is not nearly worth it when you could just modify the schema to properly implement the rules you need. There really isn't a good reason why you shouldn't have a unique constraint here, and it baffles me why there isn't one already to meet your requirements. – esqew Jan 11 '22 at 17:07
  • I accepted answer because @a_horse_with_no_name explained why it is impossible to do what a want with my conditions. And i absolutely agree with you that table schema should be changed. But i can't do it. – Valejo Jan 11 '22 at 17:24

1 Answers1

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)

  • Thanks, but my teamlead said, that i should not use subqueries. – Valejo Jan 11 '22 at 17:01
  • You can't do this without a sub-query as you absolutely need the NOT EXISTS condition. There is nothing wrong with using sub-queries - prohibiting them is really, well let's say, questionable. –  Jan 11 '22 at 17:03
  • 1
    The only way to avoid the sub-query is to add a unique constraint and use `on conflict do nothing` - that would also be way more efficient and safe for concurrent execution –  Jan 11 '22 at 17:15