I have a table with many timestamps in it, some of which may be null. Is there a way to combine them all and keep the most recent timestamp so I don't have to reference each timestamp individually?

For example, given this over-simplified example:

FROM foo
    ts1 >= '2018-04-17' OR
    ts2 >= '2018-04-17' OR
    ts3 >= '2018-04-17';

I suspect there must be a way to turn it into something like this:

SELECT blah, somefunc(ts1, ts2, ts3) AS ts
FROM foo
WHERE ts >= '2018-04-17';
  • Oooh, COALESCE gets me most of the way there. I think I can replace somefunc with COALESCE in my example, and by ordering the timestamp fields the way I need, it will return the first non-null timestamp. – Stéphane Apr 17 '18 at 17:39
  • I don't have any knowledge of postgresql but I think it is nonsense to do so for the same reasons it would be nonsens on an Oracle Db. Hiding the columns behind a function call will it make impossible for the optimizer to use indexes. – miracle173 Apr 18 '18 at 8:19
  • @miracle173 Thanks for your opinion. However, I'd argue that calling GREATEST(...), LEAST(...), or COALESCE(...) is obviously not nonsense. They exist for a reason. In my case with 8 timestamp fields to compare, it certainly makes the SQL easier to read and maintain since the resulting single timestamp is referenced several times in the WHERE, JOIN, and ORDER BY clauses. – Stéphane Apr 18 '18 at 23:34

You need the greatest() function:

SELECT greatest(now(), 
                now() + interval '1 minute', 

 2018-04-17 19:40:04.568827+02

About NULLs:

NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Please note that comparing timestamps (especially timestamps with time zones) to simple date values sometimes result in surprises. I always try to use something like date_trunc('day', '2018-01-05'::date) to get a type that can be compared cleanly.

  • Is it normal that such a "virtual" column cannot be used in the WHERE clause? ERROR: column "ts" does not exist. (I may open a new stackexchange ticket.) – Stéphane Apr 17 '18 at 18:01
  • @Stéphane yes, totally expected. There must be a plethora of questions about it already. – dezso Apr 17 '18 at 18:02
  • Yes, thank you again. Combined your greatest() solution with what I found here: stackoverflow.com/questions/2102373/… – Stéphane Apr 17 '18 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.