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