Postgres Add and Remove in Single Query

I am using Postgres. I have three tables: pictures, tags, and picture_tags

To keep it simple, here are all the table columns:

pictures.id
pictures.name
tags.id
tags.name
picture_tags.pictureId
picture_tags.tagId

A picture can have many tags. When I edit a picture record, I want to be able to add and delete picture_tags in a single query. If a user sends a list of tag entities to the server, I need to check if they should be added, deleted or ignore (if they already exist).

Right now I query the picture_tags table by picture.id, then use the results to determine what picture_tag records I have to create and which to delete. Then I run two separate calls, to add and delete. Three calls is a lot and I'm开发者_如何学JAVA trying to do this all in one call if possible. I assume doing this in a single call is more performant?

There is nothing wrong per se with doing 3 separate operations. However, you should be performing all 3 inside a single transaction. In practice, if you were executing your commands directly against Postgres, you would want to use:

BEGIN TRANSACTION;

-- your inserts/updates/deletes here

END TRANSACTION;

If you were issuing the calls from your application layer, you would also want to ensure that the commands execute inside a single transaction. For example, in Java Spring you could place all 3 operations inside a single method and then annotate it with @Transactional.