You could mark the subquery's result with a collation like this:
postgres=# SELECT 'c' COLLATE "de_DE" > (SELECT 'ç') COLLATE "es_ES"; ERROR: collation mismatch between explicit collations "de_DE" and "es_ES"
I'm not sure if this behavior is considered a bug, but I also can't imagine how it would be expected given the current documentation. It seems to me one or the other should be updated.
It seems correct to me. It does say "An explicit collation derivation occurs when a COLLATE clause is used; all other collation derivations are implicit". A subquery falls under the "all other collation derivations" category. Perhaps we could make it more clear what the COLLATE clause binds to, especially with subqueries, but I'm not sure how exactly to phrase it. Perhaps an additional example with a subquery would help?
So, I tried coming up with an example with a subquery that explains what the COLLATE clause binds to, and I realized I'm still not clear. I came up with an example using the DDL that is in the docs:
CREATE TABLE test1 ( a text COLLATE "de_DE", b text COLLATE "es_ES", ...
);
My thought was to add the following example:
SELECT a < (select 'foo' COLLATE "fr_FR") FROM test1;
I thought this would error out because the subquery's result is considered implicit and, in this case, it seems you now have conflicting implicit collations. However, this does not produce an error. What am I missing? The result of the subquery has collation "fr_FR" and, if it's implicit, then I shouldn't be able to compare it with test1.a, which has an implicit collation of "de_DE".
