PostgreSQL automatically update row at specific date

Question

I want to update a row once it reaches a specific timestamp in that row. For example, an auction site has deals that expire in a day or so. After that time comes the deal automatically expires.

Does PostgreSQL have a feature where I can set a row to automatically update when a specific time is reached? Otherwise how I would I implement this example deal feature?

No, there is no such feature. You need to create a cron job that runs an approriate update statement at regular intervals — user330315
– user330315, Commented Apr 22, 2014 at 13:01
Or design your software to not need the record to change when the date has passed. Just implement whatever needs to happen for expiration in the logic which presents it to the user, not in the database. — Dark Falcon
– Dark Falcon, Commented Apr 22, 2014 at 13:04
Take a look at pgAgent, it's a job scheduling agent for PostgreSQL http://www.pgadmin.org/docs/1.4/pgagent.html — Laura
– Laura, Commented Apr 22, 2014 at 13:09

Clodoaldo Neto · Accepted Answer · 2014-04-22 13:23:30Z

2

A view can work for that. Suppose a table t

create table t (
    deal_id integer,
    deal_start timestamp,
    expiry_time interval
);
insert into t (deal_id, deal_start, expiry_time) values
(1, now(), '1 day');

A view will calcutale if that deal has expired

create view v as
select *, now() > deal_start + expiry_time as expired
from t
;

select * from v;
 deal_id |         deal_start         | expiry_time | expired 
---------+----------------------------+-------------+---------
       1 | 2014-04-22 13:26:32.319394 | 1 day       | f

answered Apr 22, 2014 at 13:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bacon Bits Over a year ago

This is probably the best option for doing the work in the DB schema. PostgreSQL doesn't support calculated/virtualized columns in tables, so you must use a view or function instead.

Collectives™ on Stack Overflow

PostgreSQL automatically update row at specific date

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related