SQL: ORDER BY on a column with a mixed case or accent needs a collation

SELECT name FROM users ORDER BY name;

-- Case-insensitive comparison and ordering:
SELECT name FROM users ORDER BY name COLLATE "en_US";
SELECT name FROM users WHERE LOWER(name) = 'ann';

-- PostgreSQL 12+ non-deterministic collation, for a case-insensitive column:
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
                     deterministic = false);
ALTER TABLE users ALTER COLUMN name TYPE text COLLATE ci;

C collation (byte order):   Ann, Bob, Zed, ann, bob
en_US collation:            Ann, ann, Bob, bob, Zed

-- A database created with LC_COLLATE=C sorts uppercase before all lowercase.
-- This is why a list looks correctly alphabetised locally and wrong in
-- production, or the reverse.