13

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this:

update tableA
set * = tableB.*
from tableB where tableA.id = tableB.id

I tried in psql, it doesn't work. I have to list every column like this:

update tableA
set c1 = tableB.c1, c2 = tableB.c2, ...
from tableB where tableA.id = tableB.id

tableB is created use create .. like tableA. So they are basically identical. And the reason I'm doing it is that I need to load .csv data to a temp table tableB and then update tableA based on the new data in tableB. tableA needs to be locked as little as possible and tableA needs to keep integrity. I'm not sure 'delete then insert' would be a good option?

12

There is no syntax variant that lets you update the whole row at once. However, there is a shorter form than what you have so far.

Also, you do not actually want to update all columns. The WHERE condition on id pins down at least one column (id) to remain unchanged. But that's just nitpicking.

UPDATE table_a a
SET    (  c1,   c2, ...)
     = (b.c1, b.c2, ...)
FROM   table_b b
WHERE  a.id = b.id;

More details in this related answer:
Bulk update of all columns

DELETE / INSERT

Internally, due to the MVCC model of Postgres, every UPDATE effectively inserts a new row anyway and marks the old one as obsolete. So, behind the curtains there is not much difference between UPDATE and DELETE plus INSERT.
There are some details in favor of the UPDATE route:

  • HOT UPDATE.
  • TOAST tables: If you have large columns, the content may be stored."out-of-line" in TOAST tables and the new row version can link to the same row in the TOAST table if toasted columns remain unchanged.
  • Index maintenance may be cheaper for updates.

Otherwise, locking should be about the same. You need an exclusive lock on affected rows either way. Just make it quick.
If you are dealing with a huge number of rows and you don't need a consistent state (all rows or none), you can split the operation into multiple batches. (Separate transactions!) Increases the total cost, but keeps the lock time per row shorter.

  • 3
    DELETE / INSERT may also have unwanted or just different (cascade or triggered) effects than the UPDATE. – ypercubeᵀᴹ Apr 25 '14 at 8:39
  • It is correct but you should update alias part of table_a. table_a (which is updated table) cannot get an alias. – Just Another Code Lover Jun 7 '18 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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