Is there a max function for timestamps (with or without timezone)?

Question

When I call max on two timestamps, I get an error :

select max(now()::timestamp, to_timestamp('2021-01-01', 'YYYY-MM-DD')::timestamp);
-- ERROR:  function max(timestamp without time zone, timestamp without time zone) does not exist

My actual usage would be in an upsert query, where I would like to write :

INSERT ...
ON CONFLICT (pk) DO UPDATE SET
  ts = max(table.ts, excluded.ts)

Question

Is there an idiomatic way to take the max between two timestamps in PostgreSQL ?

Answer 1

I guess you are looking for the greatest() function.

demo:db<>fiddle

max() (link to docs) is an aggregate function which returns the greatest value of a group of records.
greatest() (link to docs) takes the greatest value of an arbitrary number of parameters - two in your case. It can be used for type timestamp as well.

---

extra note : the function to take the smallest value of an arbitrary number of parameters is named least() (same link) (not "smallest")

Answer 2

Use GREATEST:

SELECT GREATEST(now()::timestamp, to_timestamp('2021-01-01', 'YYYY-MM-DD')::timestamp);

The GREATEST scalar function takes one or more arguments and returns the largest value of those inputs.