CODEWITHSUNDEEP
postgresql
fresko
fresko

Reputation: 2062

Postgres: update the column with lowest value

I want to create a table with the following structure (last_seen[1,2] are timestamps):

EVENT_ID | last_seen1 | last_seen2
-----------------------------------
    2    |   120660   | 123400

I want to know the latest 2 times where an event happened. In a traditional way, I should keep the most recent timestamp in last_seen1, and write the older value in last_seen2. When a new event will arrive, i have to move the value from last_seen1 to last_seen2 and write a new value in last_seen1.

But I wonder if it's possible to do the following: when EVENT_ID = 2 will come again, I want to update the column with the lowest value. I don't know WHERE is the oldest timestamp, the query should be written in a so smart way, that it will discover the name of the column with the lowest value (between the two candidate columns: last_seen1, last_seen2).

Reading from such table is simple, because i can use MIN or MAX of values in last_seen[1,2]. But how to create a query to WRITE the column with the lowest value, if this is possible? I mean, i don't know if the name of the column where i am going to write: it can be last_seen1 or last_seen2.

Upvotes: 0

Views: 852

Answers (2)

kofemann
kofemann

Reputation: 4423

You can use LEAST to get the values:

SELECT LEAST(last_seen1, last_seen2) FROM  t WHERE event=2

or with GREATEST

UPDATE t SET last_seen1=LEAST(last_seen1, last_seen2), last_seen2=GREATEST(last_seen1, last_seen2) WHERE event=2;

or with desired newvalue

UPDATE t SET last_seen1=LEAST(last_seen1, last_seen2, newvalue), last_seen2=GREATEST(last_seen1, last_seen2, newvalue) WHERE event=2;

Upvotes: 1

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

You can use the logic of CASE WHEN expressions for this, and remember that overwriting a value with itself is harmless:

UPDATE mytable
SET last_seen1 = CASE WHEN last_seen1 < last_seen2 THEN 'newvalue' ELSE last_seen1 END,
    last_seen2 = CASE WHEN last_seen1 < last_seen2 THEN last_seen2 ELSE 'newvalue' END
WHERE event_id = 2

Upvotes: 2

Related Questions