Postgres Greatest Query not returning correct timestamp

Question

I think I might be writing this query incorrectly. Essentially, I want to find the object with the latest timestamp across four possible values (in this example, user_id is 1).

SELECT *, GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at)
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1

This will always return a time for updated_at, but even if the deleted_at time for one of the objects is the latest, it will not return a deleted_at time. I know, because running a query for deleted_at IS NOT NULL returned a later timestamp.

What am I doing wrong here?

The JOIN condition looks weird. books.user_id = #{self.id} OR posts.user_id = #{self.id}? Perhaps you wanted AND there instead of OR? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 16, 2018 at 13:16
Keep in mind that GREATEST and LEAST ignore NULL values. Have a look at Postgresql docs about it. — McNets
– McNets, Commented Nov 16, 2018 at 14:38
Perhaps it's my lack of Postgres knowledge but ... I don't see a join clause between the two tables ... do you really want a cartesian product? — markp-fuso
– markp-fuso, Commented Nov 16, 2018 at 14:52
@McNets Thank you, I appreciate that. It's no problem if they ignore all NULL values, I have that built into my code. I appreciate the heads up. — JapanRob
– JapanRob, Commented Nov 17, 2018 at 2:06
@ypercubeᵀᴹ Thank you, I appreciate it. I'm not sure that's where the issue is coming from, but let me take a look and get back to you if it is. — JapanRob
– JapanRob, Commented Nov 17, 2018 at 2:07

JapanRob · Accepted Answer · 2018-11-17 03:00:50Z

0

So, I got it figured out and I'm leaving the answer for posterity.

Essentially, remove the * from the query and add a LIMIT 1 and it all appears to work. In addition, as @ypercubetm stated, you can use AND in place of OR, but it depends on your usecase.

SELECT GREATEST(books.updated_at, books.deleted_at, posts.updated_at, posts.deleted_at) AS greatest_time
FROM books, posts WHERE books.user_id = 1
OR posts.user_id = 1 ORDER BY greatest_time DESC LIMIT 1;

returns a single, latest time.

edited Nov 17, 2018 at 3:00

answered Nov 17, 2018 at 2:37

JapanRob

1033 bronze badges

Add a comment |

Stack Exchange Network

Postgres Greatest Query not returning correct timestamp

1 Answer 1

Your Answer

Hot Network Questions

Postgres Greatest Query not returning correct timestamp

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions