postgresql – INSERT a row at most once per day

I have a Postgres table which (for simplicity) looks like:

  i          serial8 PRIMARY KEY,
  userid     int8 NOT NULL,
  date       int4 NOT NULL,
  followers  int4);

and I insert like so:

INSERT  INTO tt_series (userid,date,followers)  VALUES (%s,%s,%s)

The date column is a timestamp.

I want to modify the insertion so that the insertion is performed IFF the current date (at the moment of insertion) for a given userid is not on the same day as any value that’s in the database (for that userid). (The idea is that each userid only gets at most one row per day.)

How can that be done?

Example. Suppose the database has 2 rows, which are:

userid  date       followers
1234567 1630072910 50
1234567 1631072910 100

and then I get new data that looks like:

userid  date       followers
1234567 1631072911 101

where the timestamp has increased by 1 second (for userid 1234567, with respect to the latest insertion for userid 1234567) and the followers column has increased by 1 (for userid 1234567).

In this case the insertion should not go through because it’s on the same day as the lastest insertion for userid 1234567 (where “latest” is determined by the value of the date column.)

(Notice that we’re not saying that 24 hours or more must have passed, but only that further insertions for a given userid cannot be done on the same day. This means that the timestamp difference of 2 insertions could be less than 24 hours, say if some insertion was done at 11pm and the next insertion was done at 1am. Then the difference is 2 hours but the insertion is still valid because they’re on different days.)

(Since the day boundaries depend on the timezone, I think, assume the timezone UTC-0000.)

(Btw maybe Postgres a has more elegant way of handling timestamps and the idea of “latest insertion for a given row depending on the value of a particular column” (ie. userid), in which case that’s also a valid answer.)