By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Another pimple on the type system

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Greg Stark <gs*****@mit.edu> writes:
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


7.4 defaults this to type text:

regression=# select '' order by 1;
?column?
----------

(1 row)
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.