up vote 0 down vote favorite

We're working on migrating data from MySQL to PostgreSQL.

Sample table structure:

                     Table "public.model"
       Column       |            Type             | Modifiers 
--------------------+-----------------------------+-----------
 model_id           | integer                     | not null
 last_purchase_time | timestamp without time zone | 
Indexes:
    "model_pkey" PRIMARY KEY, btree (model_id)
Referenced by:
    TABLE "items" CONSTRAINT "items_model_id_fkey" FOREIGN KEY (model_id) REFERENCES model(model_id)

                  Table "public.items"
    Column     |            Type             | Modifiers 
---------------+-----------------------------+-----------
 item_id       | integer                     | not null
 purchase_time | timestamp without time zone | 
 model_id      | integer                     | 
Indexes:
    "items_pkey" PRIMARY KEY, btree (item_id)
Foreign-key constraints:
    "items_model_id_fkey" FOREIGN KEY (model_id) REFERENCES model(model_id)
Referenced by:
    TABLE "invoice" CONSTRAINT "invoice_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

                 Table "public.invoice"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 inovoice_id  | integer                     | not null
 invoice_time | timestamp without time zone | 
 item_id      | integer                     | 
Indexes:
    "invoice_pkey" PRIMARY KEY, btree (inovoice_id)
Foreign-key constraints:
    "invoice_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

If we've to update columns of single table using join then we can use below query in postgresql.

update model
   set last_purchase_time = now()
from items
     join invoice
        on items.item_id = invoice.item_id
where model.model_id = items.model_id
  and invoice.item_id = 2002;

Below is an update query for updating multiple columns in all tables, which works fine in MySQL.

update model
       join items
          on model.model_id = items.model_id
       join invoice
          on items.item_id = invoice.item_id
set model.last_purchase_time = now(),
    items.purchase_time = now(),
    invoice.invoice_time = now()
where invoice.item_id = 2002;

Can someone suggest an equivalent PostgreSQL query for updating multiple columns in different tables using join?

share
    
No, PostgreSQL only allows a single table to be updated with a single query. However, you can use multiple CTEs to do multiple DML queries at the same time. -- But why do you store your data redundantly? model.last_purchase_time is calculable & I see no benefit from being able to supply different timestamps for invoice & items. – pozs Dec 12 '16 at 10:56
    
@pozs The tables are more complex in my environment with each table having more than 20 columns, for example purpose alone I've simplified it and used same timestamp across tables, in real case the values will differ. – vjy Dec 12 '16 at 11:11
up vote 1 down vote accepted

You can do it like this in PostgreSQL:

WITH upd1 AS
        (UPDATE invoice
         SET invoice_time = current_timestamp
         FROM items
            JOIN model
               ON model.model_id = items.model_id
         WHERE items.item_id = invoice.item_id
            AND invoice.item_id = 2002
         RETURNING model.model_id, items.item_id),
     upd2 AS
        (UPDATE items
         SET purchase_time = current_timestamp
         FROM upd1
         WHERE upd1.item_id = items.item_id)
UPDATE model
SET last_purchase_time = current_timestamp
FROM upd1
WHERE upd1.model_id = model.model_id;

The only downside is that items and model will be updated more often than necessary.

share
    
Thanks. It worked, I'll also try to break down the logic by updating one column at a time to avoid unnecessary updates wherever its possible. – vjy Dec 12 '16 at 13:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.