Collation difference in PostgreSQL on AWS RDS and local machine

Question

I have an issue with collation in PostgreSQL when sorting a result set. We use AWS RDS PostgreSQL, and we want to sort query results in a specific way. Locally, everything works perfectly on my laptop (macOS), and all tests are passing. However, after deploying to the staging environment on AWS RDS, the sort order is different.

After some research, I realized that the "en_US.UTF-8" collation on my laptop behaves differently than it does on the AWS RDS instance.

Environment details:

• Locally: macOS, PostgreSQL 14.7
• Staging (AWS RDS): PostgreSQL 14.12

I don't think the version difference should matter much in this case.

Here is the query and result locally on my macOS machine

SELECT UNNEST(ARRAY['A', '#', 'b', '@', '🕸️spider', '!symbol', 'Try', '💨wind', 'Zzzzzz']) COLLATE "en_US.UTF-8" AS title 
ORDER BY title;

Result (local machine)

["!symbol", "#", "@", "A", "Try", "Zzzzzz", "b", "💨wind", "🕸️spider"]

• Symbols (!, #, @) appear first.
• Letters (A, Try, Zzzzzz, b) come next.
• Emojis (💨wind, 🕸️spider) appear last.

However, on staging (AWS RDS)

SELECT UNNEST(ARRAY['A', '#', 'b', '@', '🕸️spider', '!symbol', 'Try', '💨wind', 'Zzzzzz']) COLLATE "en_US.utf8" AS title 
ORDER BY title;

Result (staging environment)

["@", "#", "A", "b", "🕸️spider", "!symbol", "Try", "💨wind", "Zzzzzz"]

The order is inconsistent with the local result:

• Symbols and letters are mixed.
• Emojis don't follow the same pattern.

What I've tried/checked

1. I confirmed that both environments are using the "en_US.UTF-8" collation. However, on AWS RDS, the collation name appears as "en_US.utf8" (lowercase .utf8).
2. The PostgreSQL versions are slightly different: 14.7 locally and 14.12 on staging, but I don't think this should affect collation behavior.

Questions

• Why are the collation results different between my local macOS environment and AWS RDS?
• How can I make the sort order consistent across both environments?

Answer 1

The collation is taken from the system's C library, and it seems that there is a different C library (or a different version of the C library) in use on both systems.

If you want a consistent sorting order on both systems, I see two options:

1. Use the binary collation "C" or "POSIX". That should sort the same way if the server encoding is the same.

   But that will probably not provide the sorting order you need.

2. Use an ICU collation like "en-US". If both systems use the same version of the ICU library, that should work the same.

   Before PostgreSQL v15, you can use an ICU collation on the column level. From v15 on, you can also define an ICU collation as the database collation.