Question 90363 in PostgreSQL Question posted in
The official documentation can be found here.

Update row if column value is older than 1 hour – Postgresql

I have this table in PostgreSQL:

CREATE TABLE pet (
_id SERIAL PRIMARY KEY,
player_id int REFERENCES player(_id),
feed_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
num_poop integer DEFAULT 0,
clean_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
play_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
health integer DEFAULT 100
);

I want to make separate update queries for clean_time, play_time, or feed_time and update each with the current timestamp only if the existing value is more than an hour in the past (according to the current time of the database).
If I am able to update, I want to add 20 to the current health value without exceeding 100.

I can do this by selecting from the database, doing all conditional logic in Javascript, and updating. But can I achieve this in a single UPDATE query to PostgreSQL?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The query below worked:

    UPDATE pet
SET last_cleaned = CURRENT_TIMESTAMP, health = LEAST(health+20, 100)
WHERE _id=1 AND
  CASE
    WHEN age(current_timestamp, last_cleaned) > interval '1 hour' THEN true
    ELSE false
  END;

  2. 0 
    UPDATE pet
SET    last_cleaned = CURRENT_TIMESTAMP
     , health = LEAST(health + 20, 100)
WHERE  _id = 1
AND    last_cleaned < CURRENT_TIMESTAMP - interval '1 hour';

    Besides being simpler and shorter, the filter on last_cleaned is also "sargable", i.e. can use an index.

    Aside: it’s "last_cleaned" in your answer, but "clean_time" in the question.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search