PostgreSQL. Update junction table

Question

I want to update my junction table's row. Not literally update, insert only new tuples and drop the ones that were not inserted.

Here's my tables:

users table

-----------------------
|user_id   |FullName |
-----------------------
|1      | John    |
|2      | Michael |
|3      | Bryce   |

addresses table

-----------------------------------------
|address_id|    country    |    city     |
-----------------------------------------
|    1     |      USA      |    New-York |
|    2     |     Russia    |    Moscow   |
|    3     |    Germany    |    Berlin   |
|    4     |      UK       |    London   |

This is the Junction table to connect the two now.

"user_address"

------------------------
| user_id | address_id |
------------------------
|   1     |      1     |
|   1     |      2     |
|   2     |      3     |
|   3     |      1     |

For example I want to insert a new values in my junction and make it like this:

------------------------
| user_id | address_id |
------------------------
|   1     |      1     |
|   1     |      3     |
|   2     |      4     |

And, yeah, I can just do that:

DELETE FROM user_address WHERE address_id = 1;
INSERT INTO user_address VALUES (1, 3);

But, what if I'll have 1 million of rows, and user want to delete just one row.
In this case I'll delete all 1 million and insert 999.999.999 only for ONE row.

So, how can I insert only new rows and drop the ones that were not inserted for me?

P.S. I'm using PostgreSQL.

UPDATE
Hope this screenshots will explain my problem. Here's my junction table:
I'm trying to update users for address - change user_id in junction:

UPDATE user_address SET user_id = 100 WHERE address_id = 25 RETURNING user_id

But if I use this query it will change both user_id to 100 when I want to have only one row with user_id = 100

So, question is How to avoid duplicates in junction's table update.

Answer 1

You can do the same as for the MySQL solution you've found, just that the syntax is different.

For the INSERT, you should add a UNIQUE INDEX:

CREATE UNIQUE INDEX idx_address_users ON user_address (address_id, user_id);

...and then you can use the ON CONFLICT clause (introduced in PostgreSQL 9.5), to have the duplicate rows being ignored, e.g.:

INSERT INTO user_address (user_id, address_id)
VALUES (100, 25), (101, 25), (102, 25)
ON CONFLICT (idx_address_users) DO NOTHING -- ignore duplicate new rows
RETURNING user_id;

DELETE FROM user_address WHERE address_id = 25 AND user_id NOT IN (100, 101, 102);

...or from the user side:

INSERT INTO user_address (user_id, address_id)
VALUES (100, 25), (100, 26), (100, 27)
ON CONFLICT (idx_address_users) DO NOTHING -- ignore duplicate new rows
RETURNING address_id;

DELETE FROM user_address WHERE user_id = 100 AND address_id NOT IN (25, 26, 27);

However, both of these approaches require your application to do a full load-modify-save cycle as you are dealing with complete collections here.

Besides that, the additional primary key is pretty much useless.

Answer 2

First to avoid and prevent duplicates in your "junction table", you need to make the rows unique by adding a constraint:

ALTER TABLE user_address ADD CONSTRAINT uq_user_addr UNIQUE(user_id, address_id)

-- OR if there is no primary key in your user_address table

ALTER TABLE user_address ADD PRIMARY KEY (user_id, address_id)

This will ensure that you have no duplicates in the specified table. In order to delete a single row, you will then have to specify both column values. If you want to delete all rows with a specific user_id then you specify the user_id; If you want to delete all rows with address_id you specify the address_id.