I'm trying to learn and understand collations and how postgresql compare and order strings but I'm finding an incongruity and I don't know what I'm missing.
The query:
SELECT datname, datcollate FROM pg_database;
Returns that all my DB uses en_GB.UTF-8 collation.
And the query:
SELECT table_schema,
table_name,
column_name,
collation_name
FROM information_schema.columns
WHERE collation_name is not null
ORDER BY table_schema,
table_name,
ordinal_position;
Returns that all columns have 'C' collation, which means (according to my research) that no collation is specified. So, I supose that the DB collation is used, isn't?
EDIT: My mistake is here. The previous query doesn't return a result for all the columns, only for those which collation_name
is not null, and the columns with null collation are the ones that inherit the DB collation. So I was thinking that foo.bar columns had C
collation but was null
in fact.
Well, the incongruity comes when I run the query:
SELECT "name" FROM foo.bar ORDER BY "name" ASC;
Where "name" column datatype is text and foo.bar is a user created schema.table combination. The result it's human-like alphabetically ordered. From a/A to Z/z, no matter if it's upper or lowercase.
But if I run the following query:
SELECT "table_name"
FROM information_schema.tables
WHERE
"table_name" ~ 'some_pattern'
AND table_schema = 'foo'
AND table_type = 'BASE TABLE'
ORDER BY "table_name" ASC;
It orders the result in machine-like way, comparing byte by byte and therefore ordering uppercase before lowercase.
Why there is that difference? I've seen that the "table_name" column datatype isn't "text" but "name", but I can't find if it has something to do.
Having the DB collation set to en_GB.UTF-8 shouldn't be enough to compare text in a human way?
Thanks for your time.