0

I'm running Postgres 12 on both my local machine and on an AWS RDS instance.

I have a query like

SELECT name FROM my_table WHERE (...) ORDER BY name;

"name" is a varchar(255), with a UNIQUE constraint.

When I run this on RDS, the rows are ordered like

bun.df_baa6_g900_a13500_pd20
bundle_high_basic

but when I execute it locally, the rows are flipped. It's

bundle_high_basic
bun.df_baa6_g900_a13500_pd20

It's a bit of a head scratcher for me, I haven't found any documentation about how to configure ORDER BY outside of the query itself. So these two should be returning the same order...

Does anyone have a clue why this might be happening? In terms of resolution, I don't care what the order is, as long as both machines are consistent.

I have tried amending the query with

ORDER BY lower(name)

but the same inconsistency happens with that.

Randi Trigger
  • 58
  • 4
  • 3
    check locale `LC_COLLATE` in database definition. `show LC_COLLATE` – Kadet Jun 14 '22 at 18:59
  • Ah, they are different! Local is "C.UTF-8" and RDS is "en_US.UTF-8". I don't know the first thing about these values, so I'll start researching that. – Randi Trigger Jun 14 '22 at 19:14
  • Ok, I got it figured out. Thanks again! For the short term, I could have updated my query to `ORDER BY (name COLLATE "en_US")`, but I have other queries that order by text, so I didn't favor that. My resolution was to update my local Postgres container for en_US. More-or-less following the answer in this [post](https://stackoverflow.com/questions/28405902/how-to-set-the-locale-inside-a-debian-ubuntu-docker-container) – Randi Trigger Jun 14 '22 at 20:22

1 Answers1

2

That is normal. Probably the database are using different collations. Compare the values of the lc_collate parameter on both databases.

But even with the same collation there could be differences if the machines are using different C libraries or different versions of the same C library. Of course you won't be able to figure out the C library version on a hosted database...

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263