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

[Derek Poon <derekp@xxxxxxxxxx>]

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?


 [1]: http://en.wikipedia.org/wiki/Fizz_buzz