Does the whole table UPDATE (without specifying WHERE clause) lock a table in PostgreSQL? E.g. does it prevents rows from being deleted / inserted?
E.g. if I run
UPDATE t1 SET key = 'value'
Can I expect no new rows will be inserted into t1 during the UPDATE execution?
If no, can I expect UPDATE will update even the rows appeared after its start? (key has no DEFAULT 'value' in its definition)
Best Answer
An UPDATE without a WHERE clause will lock all rows in the table, but will not lock the table itself for DML.
The rows can not be deleted from a different transaction because they are locked.
But you can insert new rows without problems (assuming they do not violate any constraints).
Any row that is inserted after the UPDATE will not be seen by the UPDATE statement and thus they won't be changed.