Postgresql Auto Update Column

Question

For example, if I have a table with structure like this:

id	card_number	status	created_at	updated_at
1	123	registered	2024-07-10 15:00:00.000	2024-07-10 15:00:00.000
2	456	approved	2020-04-01 15:00:00.000	2020-04-01 15:00:00.000

When a user update its card status to approved, I want in exactly 1 days (24 hours) after that, the status automatically changed to activated. Is there any way I can do this natively with SQL query or do I need some PostgreSQL extension?

Answer 1

There are no timeout triggered activities in PostgreSQL, but the canonical relational database solution to that problem is a different one anyway: define a view with a column status defined like

CASE WHEN status = 'approved' AND
          updated_at < current_timestamp - INTERVAL '24:00:00'
     THEN 'activated'
     ELSE status
END

Alternatively, build that expression into your query.