Using auto incremented IDs in query with the help of With queries in PostgreSQL

Let’s say you have following merchants table with auto incremented id:

| id            | name          |
| ------------- | ------------- |
| 1             | Merchant-1    |
| 2             | Merchant-2    |

You also have the following products table with auto incremented id:

| id            | name          |
| ------------- | ------------- |
| 1             | Product-1     |
| 2             | Product-2     |

You keep all the products that merchants sell in merchant_products table as:

| merchant_id   | product_id    |
| ------------- | ------------- |
| 1             | 1             |
| 1             | 2             |
| 2             | 1             |

If you want add new merchants and new products and use the ids of these new merchant and product to insert new records to merchant_products table, you can take advantage of WITH statements in PostgresSql

WITH newMerchant AS (
    INSERT INTO merchants (name)
        VALUES ('New Merchant')
        RETURNING id),
     firtNewProduct AS (
         INSERT INTO products (name)
             VALUES ('First New Product')
             RETURNING id),
     secondNewProduct AS (
         INSERT INTO products (name)
             VALUES ('Second New Product')
             RETURNING id)
INSERT
INTO merchant_products(merchant_id, product_id)
VALUES ((Select id from newMerchant), (Select id from firtNewProduct)),
       ((Select id from newMerchant), (Select id from secondNewProduct));

After executing the query, the values on the tables will be:

Merchants:

| id            | name          |
| ------------- | ------------- |
| 1             | Merchant-1    |
| 2             | Merchant-2    |
| 3             | New Merchant  |

Products:

| id            | name              |
| ------------- | ------------------|
| 1             | Product-1         |
| 2             | Product-2         |
| 3             | First New Product |
| 4             | Second New Product|

Merchant_products:

| merchant_id   | product_id    |
| ------------- | ------------- |
| 1             | 1             |
| 1             | 2             |
| 2             | 1             |
| 3             | 3             |
| 3             | 4             |