I have a column within a table that can take one of these three values: 'past', 'present', 'future'
However only one row can have the 'present' value. This is being checked by a simple unique constraint-index. Still, I want when the time comes someone to manually make a 'future' row to 'present' row, to trigger a function. After creating the trigger and trying to test it by manually updating a 'future' row to 'present' row, as expected i got the following error:
ERROR: duplicate key value violates unique constraint "semester_status_present"
DETAIL: Key (semester_status)=(present) already exists.
Thinking it for a minute that's completely logical, but what can I do in this case? Is the index out of place?
this is the index-constraint:
CREATE UNIQUE INDEX semester_status_present
ON public."Semester"
USING btree
(semester_status)
WHERE semester_status = 'present'::semester_status;
thanks in advance!
EDIT!!!!: The trigger converts the previous present row into past row too! However the trigger is after update on "Semester" since it avoids infinite update loop
Use a before trigger so that you can change the existing present row to a past row before the update tries to create a second present row and breaks the constraint.
Trigger function.
create function swap_present() returns trigger as $$
begin
update "Semester" set semester_status = 'past'
where semester_status = 'present'::semester_status and id != new.id;
return new;
end;
$$
language plpgsql;
I've put a check on new.id on there so that the trigger doesn't change the row you're changing. Replace this with whatever the unique id on your table is.
Trigger definition.
create trigger semester_trigger before insert or update on "Semester"
for each row
when (new.semester_status = 'present'::semester_status)
execute procedure swap_present();
The when clause means we only worry if you're changing or inserting a present row.
Since it is an after trigger, the duplicate value occurs when the future becomes the present row, but before the present row has not been changed to the past.
Try changing your trigger to before. That would mean there would be a moment in time in which there is no 'present' row (present is changed to past, future has not yet been changed to present).
I expect you would want this to run per statement rather than per row. You'll probably want to add a condition to only fire when a future row is being changed to a present row. That way, adding new future rows or changing present/past rows will not apply the trigger.