Can PostgreSQL UPDATEs in autocommit mode deadlock? Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)
That is certainly possible if the update affects more than a single table row.
This can come to pass in two ways:

The query modifies more than a single row in the target table.

The updated table has a foreign key constraint to another table. In that case, you may end up with a FOR KEY SHARE lock on the referenced table.


The deadlock can happen if your statement successfully locks a couple of rows and then blocks on a lock on some other row.