I think I understand why this happens. But I find it annoying. I don't see any
nice way around it except putting explicit casts everywhere in the code, which
bothers me.
slo=> select '' order by 1;
ERROR: Unable to identify an operator '<' for types '"unknown"' and '"unknown"'
You will have to retype this query using an explicit cast
There's no string quotation of any sort beyond the overloaded single-quote
format is there? So there's no way for me to safely put strings that can be
empty in a query that won't produce errors of this sort unless I put ::text
(or ::varchar) after every single string?
I think what's bothering me here is how the way postgres treats quotes seems
to be backwards from the way every other language treats them. In every other
language unquoted constants are handled by the parser and a best guess as to
the desired type is made. Quoted constants are explicitly determined by the
types of quotes.
So for example in C double-quotes indicate strings and single-quotes indicate
characters, but bare constants can be interpreted as various datatypes
depending on the size of the number and whether it starts with 0 or 0x or ends
in U or UL etc.
In postgres bare constants are treated as if they're explicitly a particular
integer datatype, whereas quoted constants are typed using a best-guess
heuristic. The quotes mean the exact opposite of what we usually think of
"quotes" as meaning, namely, "this means exactly what I've written, it's not
subject to further interpretation".
If it's too late to rethink this then I think it definitely warrants an
extensive explanation right at the start of the SQL language section of the
manual. Though it could be there already, I usually don't read manuals :)
--
greg