当前位置: 动力学知识库 > 问答 > 编程问答 >

php - Postgres - Combining SELECT and UPDATE

问题描述:

I have a table with messages in a Postgres DB.

When the user reads a message or a group of messages that belong together, I collect the message ids and, after the SELECT query, UPDATE the read flag to TRUE.

So it's just two simple queries, still memory-friendly. But I wonder if it could be improved anyhow, maybe by combining the SELECT and UPDATE query? I use PHP/PDO for the database access.

网友答案:

There is the RETURNING clause of the Postgres UPDATE statement:

UPDATE msg_tbl
SET    read = TRUE
WHERE  msg_id = ?
RETURNING *;

But that is only an improvement if all rows actually need an UPDATE. If some already have read = TRUE that would cause empty updates ending up more expensive than a SELECT and a separate UPDATE with an additional WHERE read = FALSE.

分享给朋友:
您可能感兴趣的文章:
随机阅读: