CODEWITHSUNDEEP
postgresqlsql-order-byamazon-rds
Randi Trigger
Randi Trigger

Reputation: 68

Postgres ORDER BY giving inconsistent results across machines (local and RDS)

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.

Upvotes: 0

Views: 272

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246453

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...

Upvotes: 3

Related Questions