Andy Carlson Andy Carlson - 1 month ago 4
Linux Question

Postgres sorting on timestamp works on mac but not linux

Using Postgres 9.4

I have a posts table which relates to a users table. I'm querying for two users and 3 of their most recent posts.

SELECT
"users"."id" AS "id",
"posts"."id" AS "posts__id",
"posts"."created_at" AS "posts__created_at"
FROM (
SELECT * FROM accounts
WHERE TRUE
ORDER BY "id" ASC
LIMIT 2
) AS "users"
LEFT JOIN LATERAL (
SELECT * FROM posts
WHERE "users".id = posts.author_id
ORDER BY "created_at" DESC, "id" DESC
LIMIT 3
) AS "posts" ON "users".id = "posts".author_id


On mac, the order is as expected.

"2016-04-17 18:49:15.942"
"2016-04-15 03:29:31.212"
"2016-04-13 15:07:15.119"


I get descending order on
created_at
, which is a
timestamptz
. However, when run on my travis build, which is Ubuntu, the ordering is stable, but neither ascending nor descending....

"2016-04-15 03:29:31.212"
"2016-04-13 15:07:15.119"
"2016-04-17 18:49:15.942"


I made user to create the databases with the same
LC_COLLATE = en_US.UTF-8
with no luck. Why on earth isn't the ordering working on travis?

Answer

To solve this, just add the order by statement under your existing statements above. i.e.

SELECT
  "users"."id" AS "id",
  "posts"."id" AS "posts__id",
  "posts"."created_at" AS "posts__created_at"
FROM (
  SELECT * FROM accounts
  WHERE TRUE
  ORDER BY "id" ASC
  LIMIT 2
) AS "users"
LEFT JOIN LATERAL (
  SELECT * FROM posts
  WHERE "users".id = posts.author_id
  ORDER BY "created_at" DESC, "id" DESC
  LIMIT 3
) AS "posts" ON "users".id = "posts".author_id
order by posts.created_at desc

The order of output on postgres (and many other dbms's) cannot be guaranteed without an order by statement.

While you do indeed have order by statements, they are within sub-queries, you need the order by on the outer query.