1

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?

Hash
  • 4,647
  • 5
  • 21
  • 39
Irlanco
  • 769
  • 1
  • 8
  • 22
  • 6
    No, there is no such feature. You need to create a cron job that runs an approriate `update` statement at regular intervals –  Apr 22 '14 at 13:01
  • 3
    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 Apr 22 '14 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](http://www.pgadmin.org/docs/1.4/pgagent.html) – Laura Apr 22 '14 at 13:09

1 Answers1

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
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 1
    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](http://stackoverflow.com/questions/8250389/computed-calculated-columns-in-postgresql) instead. – Bacon Bits Apr 22 '14 at 13:48