CODEWITHSUNDEEP
sqlpostgresqljunction-table
Nikita Kalugin
Nikita Kalugin

Reputation: 742

PostgreSQL. Insert valuse in junction table

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.

Upvotes: 0

Views: 460

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

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

Upvotes: 3

Related Questions