Re: Implicit cast of literal in SQL statements

Scott Royston <scroyston71(at)yahoo(dot)com> 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