Does UPDATE without WHERE clause lock a table in PostgreSQL?

Question

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)

• Joe W: If it was updating rows that where inserted after it started and there was a process that was continually inserting rows wouldn't that mean the update statement would never finish?
• origaminal: good point! Yes, it is barely possible UPDATE to be implemented in such manner to update newly inserted rows.

Answer 1

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.

• SilverbackNet: @origaminal The doc in question is at postgresql.org/docs/current/explicit-locking.html , the last four are what you're calling 'table locks' -- although even permissive locks are still a form of table lock that will prevent attempts to take an exclusive lock.