Re: Why would I need to explicitly cast a string literal to text?

Derek Poon-2 wrote
> As an exercise, I've written the following query to implement
> [FizzBuzz][1].
>
> SELECT COALESCE(fizz || buzz, fizz, buzz, '' || n) AS fizzbuzz
> FROM (
> SELECT n0 + 3 * n3 + 9 * n9 + 27 * n27 + 81 * n81 AS n
> FROM
> (SELECT 0 AS n0 UNION ALL SELECT 1 UNION ALL
> SELECT 2 AS n0) AS N0,
> (SELECT 0 AS n3 UNION ALL SELECT 1 UNION ALL
> SELECT 2 AS n3) AS N3,
> (SELECT 0 AS n9 UNION ALL SELECT 1 UNION ALL
> SELECT 2 AS n9) AS N9,
> (SELECT 0 AS n27 UNION ALL SELECT 1 UNION ALL
> SELECT 2 AS n27) AS N27,
> (SELECT 0 AS n81 UNION ALL SELECT 1
> AS n81) AS N81
> ) AS N
> LEFT OUTER JOIN
> (SELECT 3 AS fizzstep, CAST('Fizz' AS CHAR(4)) AS fizz) AS
> Fizz
> ON n % fizzstep = 0
> LEFT OUTER JOIN
> (SELECT 5 AS buzzstep, CAST('Buzz' AS CHAR(4)) AS buzz) AS
> Buzz
> ON n % buzzstep = 0
> WHERE n BETWEEN 1 AND 100
> ORDER BY n;
>
> I realize that it could be vastly simplified using GENERATE_SERIES(), but
> I'm aiming for the solution to be portable to SQLite 2, SQLite 3, and
> MySQL as well.
>
> I'd like to know, why are the two explicit casts necessary? Casting to
> VARCHAR or to TEXT also works. However, if I omit the casts, I get…
>
> ERROR: failed to find conversion function from unknown to text: …
>
> I would expect that PostgreSQL should be able to infer that the fizz and
> buzz columns were some kind of text. (What else could they be?) It seems
> like a design flaw to require a literal string to be cast to text, right?

Because 'Buzz' is an "unknown literal" and not a "literal string".

The parser attempts to infer the actual type of the unknown (e.g. date,
text, boolean, etc...) but if it cannot it simply tags it as an unknown type
and because implicit type conversions are now avoided when possible that
unknown type becomes effectively useless for anything but display.

The parser cannot infer the types in this situation because the sub-query in
which the literal appears is evaluated without knowing that eventually the
fizz/buzz columns are going to be used in a string concatenation and aside
from that there is nothing else to provide it a hint of the final type the
unknown literal may take.

It has been argued before that this indeed is a design flaw but no one is
really willing to invest the effort to modify such a deeply entwined part of
the system. There is probably lots more history here that others could
reference on why such a change would be problematic to effect.

For reference: <date '2014-07-21'> is a valid literal having a type "date"

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-would-I-need-to-explicitly-cast-a-string-literal-to-text-tp5811823p5812247.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.