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
.