Thread: Why would I need to explicitly cast a string literal to text?
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
SQLite2, 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
omitthe 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
elsecould 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
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.
Derek Poon <derekp@ece.ubc.ca> wrote:
> 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
> 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?
They may look like what the SQL standard calls a <character string
literal>, but in PostgreSQL, due to the heavy use of custom types,
we treat it as being of type "unknown" for as long as we can and
use the "input" routine for the type which it seems to be. This
helps people use custom types more as "first class types". For
example:
test=# create table area(id int primary key, rectangle box not null);
CREATE TABLE
test=# insert into area values
test-# (1,'((1,10),(2,20))'),
test-# (2,'((20,40),(60,80))');
INSERT 0 2
test=# select * from area where rectangle = '((1,10),(2,20))';
id | rectangle
----+---------------
1 | (2,20),(1,10)
(1 row)
test=# select 10 + '-3';
?column?
----------
7
(1 row)
In my example above, it is clear from the context what type the
"unknown" literals should be. In your example the planner is not
able to make the determination in time to avoid an error. Maybe
that can be fixed for this particular case, but in general
PostgreSQL needs occasional type casts in situations where other
DBMSs don't, in order to be able to omit them in many cases that
other products simply don't support.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company