I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12

The query is the following :

UPDATE folder as f set locked = 'locked', date_locked = current_timestamp 
from (
    SELECT * from folder as f2 
    WHERE f2.locked = 'available'
    LIMIT 1 FOR UPDATE ) as folderToUpdate 
WHERE f.id = folderToUpdate.id returning f.*

The id is the primary key.

It is executed through a bunch of integration tests. When I am executing only the test doing the query above, I don't have any issue. But, when I am executing all the tests, I randomly get multiple rows. Do you have any suggestions?


It's quite normal you get multiple rows. The update selects all rows from folder, and for each of them selects a row from folder f2 (I guess the same, if id is PK). If locked = 'available', the record is updated. On to the next row.

The correct syntax is here, Postgres UPDATE ... LIMIT 1, and consists in using a CTE.

Without CTE, your result depends on how PostgresSQL decides to execute your update, starting with the outer (multiple rows are possible) or with the inner select.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.