Scott Royston <[EMAIL PROTECTED]> writes: > I've seen a few postings in multiple newsgroups saying that in 7.1.x and > up, literals in SQL statements are implicitly cast to strings.
That's an oversimplification: the implicit coercion of unknown literals only happens when looking for an operator or function to apply to them. For an unprocessed result literal such as you describe, the type never does get changed. Which is okay because type "unknown" does have an output routine, which is all that's needed to emit the literal. You may care to peruse the rules in http://developer.postgresql.org/docs/postgres/typeconv.html > However, in both 7.1.3, and a fresh build of 7.2b4 from cvs, (with all > regressions passing) I get: > mytest=# select distinct 'hello' from mytable; > ERROR: Unable to identify an ordering operator '<' for type 'unknown' > Use an explicit ordering operator or modify the query This is mildly annoying but I'm not sure that fixing it wouldn't introduce greater annoyances. As an example of the pitfalls, consider: regression=# select 1 union select '2'; ?column? ---------- 1 2 (2 rows) regression=# select 1 union select '2'::text; ERROR: UNION types "int4" and "text" not matched The first example works because the right-hand SELECT's result is not coerced to "text" before UNION can get its hands on it. Possibly DISTINCT should be allowed to type-coerce unknown inputs to text the same way that explicit operators and functions can. Offhand I'm not sure if that's a good solution or not. There are related cases to consider too, eg ORDER BY and GROUP BY. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
