Postgresql ORDER BY not working as expected

Question

Let's try this simple example to represent the problem I'm facing.

Assume this table:

CREATE TABLE testing1
(
    id serial NOT NULL,
    word text,
    CONSTRAINT testing1_pkey PRIMARY KEY (id)
);

and that data:

insert into testing1 (word) values ('Heliod, God');
insert into testing1 (word) values ('Heliod''s Inter');
insert into testing1 (word) values ('Heliod''s Pilg');
insert into testing1 (word) values ('Heliod, Sun');

Then I want to run this query to get the results ordered by the word column:

SELECT
    id, word
FROM testing1
WHERE UPPER(word::text) LIKE UPPER('heliod%') 
ORDER BY word asc;

But look at the output, it's not ordered. I would expect the rows to be in that order, using their ids: 2, 3, 1, 4 (or, if I use the word's values: Heliod's Inter, Heliod's Pilg, Heliod, God, Heliod, Sun). This is what I get:

I thought that maybe something could confuse postgresql because of the WHERE criteria I used, but the below happens if I just order by on the rows:

Am I missing something here? I couldn't find anything in the docs about ordering values that contain quotes (I suspect that the quotes cause that behaviour because of their special meaning in postgresql, but I may be wrong).

I am using UTF-8 encoding for my database (not sure if it matters though) and this issue is happening on Postgresql version 12.7.

The output of show lc_ctype; is

"en_GB.UTF-8"

and the output of show lc_collate; is

"en_GB.UTF-8"

Answer 1

That is the correct way to order the rows in en_US.UTF-8. It does 'weird' (to someone used to ASCII) things with punctuation and whitespace, skipping on a first pass and considering it only for otherwise tied values.

If you don't want those rules, maybe use the C collation instead.