PostgreSQL. Insert valuse in junction table

Question

Good day. I'm having some trouble with insert data in junction table.

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     |

I want to connect them then I create address. So, I need to create a new address in address table and them, put ID of created address in junction (don't care about user_id, I just need to handle address_id).

Here's a query which I used for address creation:

"INSERT INTO addresses (country, city) VALUES (?, ?) RETURNING id, country, city"

As you can see, I need to return values of created address to show them to my consumer.

How can I insert a new Address, get it's ID and put it in my junction? Desirable within a single query.

The `user_ids` in `users` and the junction table do not match. How are you supposed to relate each other? — Kaushik Nayak, May 24 '19 at 03:29
@KaushikNayak I'm using Java. My address entity have a Set of user_ids (but user don't know about his address - one way binding, and I use this set only for java part, so there's no set or something in my table, just fields listed above), and when I create address I'll get user_ids from this set and put them in junction too as a id of created address. — Nikita Kalugin, May 24 '19 at 04:00

score 3 · Accepted Answer · answered May 24 '19 at 04:02

A with clause insert would help.

with ins AS
(
 INSERT INTO addresses (country, city) 
     VALUES ('USA', 'New-York') RETURNING address_id, country, city
),
ins2 AS
(
 INSERT INTO user_address (address_id) select address_id from ins
)
select * from ins

Note:

You said

.. don't care about user_id, I just need to handle address_id

So, I presume you've got another mechanism to update user_ids

DEMO

PostgreSQL. Insert valuse in junction table

1 Answers1