CODEWITHSUNDEEP
linuxpostgresqltravis-ci
Andy Carlson
Andy Carlson

Reputation: 3909

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?

Upvotes: 0

Views: 53

Answers (3)

Jon Scott
Jon Scott

Reputation: 4354

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.

Upvotes: 1

user268396
user268396

Reputation: 11976

Because the actual sort order depends on both the order of id in the first table and the order of the created_at & id in the second one prior to joining them. This means the order of the first table can produce unexpected results when computing the selected values from the joined table.

To fix the sort order, you should sort the final result set by relevant columns as well.

Upvotes: 1

Jayvee
Jayvee

Reputation: 10875

you may need to order the outer query too because the in join between the 2 inner queries, even when they are ordered, won't be guaranteed.

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

Upvotes: 1

Related Questions