Is it possible to make an isactive field in sql so that only one record can be active at a time?

Question

PostgreSQL

I have toggle active records on the front. Now I do this, when I receive a request to activate a record, on the backend I give a request to the database to remove the activity in the database records, and put the activity in the right one.
It turns out two requests to the database, and somehow the feeling that this is wrong. Maybe I'm winding myself up, but I think there is already some kind of solution for this in sql? Or does everyone do it through two requests?
I couldn’t google, I don’t know how to make a request so that it doesn’t give information on the settings, but on the situation.

Answer 1

Is it important to have a flag?
You can do it differently - we start a DATE type field, call it ACTIVATED (for example), and when the record is activated, we write the current date into it. Then, when we need to find the active record, we simply look for the record with the oldest date.
Setting the active record, thus, is obtained by a single request, the selection is also obtained, plus, as a bonus, we get information when this record became active.

Answer 2

I would suggest using triggers, but a lot of people don't like them. business logic is blurred between the application and the database.
But if this fact does not scare you, then the solution is very good: you set it up once and forgot it, and you don’t have to worry about changing activity.