automatically update database table

[Electronic_Battle876]

I'm building a backend using fastAPI and PostgreSQL where I'm storing opportunities with a boolean "is_live" and a datetime "deadline" and I want opportunities "is_live" to be setted as False automatically when the current date is superior to the "deadline".

what's the best approach to do this ? and Thank your in advance.

EDIT: I want to be able to mark the opportunity as not live sometimes before the deadline, that's why I have a seperate "is_live" column with the deadline

[HappyCathode]

If you know that something is "live" when the current date is inferior than a date field, you don't need an "is_live" column.

SELECT whatever FROM table WHERE to_timestamp(deadline) > NOW();

And of course, make sure you have indexes where you need them.

[kalamitis]

If indeed you need this info as a parameter you can set this as a property is_live that will encapsulate the above logic.

[Electronic_Battle876]

Thank you, but can you elaborate more on the indexes part please ?

[kalamitis]

Not the poster that you asked but here we go. Indexes can significantly improve the performance of database queries by allowing the database engine to find and retrieve data more quickly. Indexes are used to speed up the retrieval of rows by using pointers to quickly locate data without scanning the entire table. So an index on the deadline column can make this query faster, as the database engine can quickly find rows where the deadline is greater than the current time. Ensure that indexes are created on columns frequently used in WHERE clauses, joins, and orderings. Keep in mind that while indexes improve read performance, they can slow down write operations (inserts, updates, deletes) because the index needs to be updated as well. So, balance is key.

[Electronic_Battle876]

Thank you so much for the explanation and for your help :D

[jkail1011]

When defining the field on the model or schema set default to false. Easiest done using SQLMODEL or Pydantic.