Updating a column in postgresql based on a specific date

Question

I'm wondering if I could use trigger with PostgrSQL to update a column where I do have column with date type and on this date I would like to updated another column in another table.

To make it more clear

I do have 2 tables

Works_locations table

walphid         wnumberid   locationid
DRAR            1012          101
PAPR            1013          105
PAHF            1014          105
ETAR            1007          102
DRWS            1007          102

the locationsid attribute refers to "locid' in Locations table which is down

locid   locname     
101     Storage 
102     Gallary A   
103     Gallary B   
104     Gallary C   
105     Lobby

Exhibition table

exhid       exhname       description       strtdate        endDate
101         Famous         Blah Blah        2013-07-15      2013-10-13

and here are bridge table to connect the exhibition table with the locations table

locationsid     exhibitid
102             102
103             101
104             103

Now Each exhibition has some works and should be placed in one of the locations table.

On the 'endDate' column in the exhibition table, which is a date data type, I would like to update 'locationid' column in the Works locations table to be placed in another location.

In another words. Each exhibitions has some works and this works are placed in one of the locations. at the ending date of the exhibition, I would like to change the locations, and specifically, I would like the work to be returned to the storage.

Any idea how would I do this action with postgresql?

Regard

You can write a trigger to update "work locations" when "exhibition"."endDate" changes. There are examples in the docs. (Scroll down.) It's not clear a) which row in "work locations" you want to change, and b) what the current date (or some current date column) has to do with this. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Oct 9, 2014 at 2:55
I just want an idea on how to do such thing which is based on current date. I did look at that page before but I didn't find an answer — Alex
– Alex, Commented Oct 9, 2014 at 3:50
Your question doesn't make sense in American English. The phrase based on the current date is unclear. You need to be more specific about a) what tables and columns you want to update, b) what values you want them to be updated with, and c) when you want them to be updated. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Oct 9, 2014 at 4:14
If you want something to happen on or after the value that's stored in "endDate", you don't want a trigger. Triggers run only when there's a change to the data. Instead, you want an UPDATE statement run manually, or run by cron, or run by pg_agent. — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Oct 9, 2014 at 6:17

Craig Ringer · Accepted Answer · 2014-10-09 07:12:04Z

4

PostgreSQL does not have a built-in task scheduler. Even if there was a scheduler, the commands it ran wouldn't be triggers, they'd just be be procedures run by a scheduler.

You can't write triggers that fire at some arbitrary time.

You will need to use cron, Task Scheduler, PgAgent, or similar to run the statements at the desired time. Or you could write a script that checks when the next event is, sleeps until then, runs the desired command and marks that event as done, and sleeps until the next event.

answered Oct 9, 2014 at 7:12

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

Collectives™ on Stack Overflow

Updating a column in postgresql based on a specific date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related