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

Derek Poon <derekp(at)ece(dot)ubc(dot)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