how can i delete if row exists (where eq(xxx)), otherwise insert, in one query?
would i have to use raw sql & an if/else statement?
15 Replies
I don't think this is something any SQL dialect supports doing in one query. Could you explain your use case?
i have a table of collections (music singles, eps, albums) that you can like. i also have a liked_collections table that just has user_id and collection_id columns. when a collection is liked/unliked, i want to either remove a row if it's already liked, or insert a row if it's not already liked
Ah, I see
i think this should work? https://www.commandprompt.com/education/postgresql-if-else-statement-with-examples/
I would actually recommend not doing this "toggling" on the DB level
This seems likely to introduce synchronization issues
ooh okay
how should i do it?
Imagine that your user loads the app on their phone and PC
Both show the song as liked
They unlike on their PC, but their phone is offline
You then unlike on their phone
When the phone comes back online and you trigger the toggle
It is back to being liked, which was not the user's intent!
i see
hmm
Have the client request specifically to like or unlike
yeah true
If the delete fails because the record doesn't exist? A-ok. If the insert fails because the row already exists? Also fine!
that's much smarter, thanks :)
yw 🙂
easier to implement as well
The best solutions to hard problems is to make your problem easier 😄