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?
Best Answer
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.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 betweenUPDATE
andDELETE
plusINSERT
.There are some details in favor of the
UPDATE
route: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.