Re: How to implement expiration in PostgreSQL?

On Thu, 1 Apr 2021 09:51:38 +0800
Glen Huang <heyhgl(at)gmail(dot)com> wrote:

Hi,

If you are in a position to modify your design, I believe your problem comes from this part :

> WHERE paid OR join_time > now() - ‘1h’::interval

which suggests that there is a 'paid' column being updated. I learned that the proper way to structure a database to maintain membership and save a lot of grief is the following :

create table seated_member(
name text not null,
...,
join_time date not null default now,
validity integer not null default 1
);

where validity is a number of whatever unit is appropriate; typically 'year' for a club membership (newspapers use 'issue number' instead of join_time to account for strikes, when no paper is issued). In your case, 'hour' I suppose.

All you need to do when the member pays is to update the 'validity' field with the proper amount of units. This makes for very simple and efficient queries to retrieve the data, and you only need to write :

WHERE now() < join_time + 'validity hours'::interval

to retrieve valid accounts.

Accounts expire automatically, deleting them can wait; it also makes it easier to send reminders before the expiration date

--
Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance