From: | Derek Poon <derekp(at)ece(dot)ubc(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Why would I need to explicitly cast a string literal to text? |
Date: | 2014-07-16 09:59:22 |
Message-ID: | CEB5D833-B064-410D-92F0-E270151949F1@ece.ubc.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Shaun Thomas | 2014-07-16 14:08:20 | We've been affected by a pg_upgrade bug. What do we do next? |
Previous Message | Marc Mamin | 2014-07-16 09:36:52 | Re: php password authentication failed for user ... |