Update all columns from another table

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?

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 that column 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 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 UPDATE and DELETE + INSERT are similar. There are some details in favor of the UPDATE route, though:

• H.O.T. UPDATE

• TOAST tables: If you have large columns, the content may be stored "out-of-line" in TOAST tables. A new row version can carry over the same link to the same row in the TOAST table if that toasted field remain unchanged.

• Index maintenance may be cheaper for updates.

Locking overhead is about the same. You need an exclusive lock on affected (existing) rows either way. So keep the transaction brief.

Different triggers are fired (if any). That may or may not be ok.

If you are dealing with a huge number of rows and you don't need a consistent outcome (all rows or none), you can split the operation into multiple batches - in separate transactions. Increases the total cost, but keeps the lock time per row shorter.

StackExchange Database Administrators Q#63871, answer score: 15