Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Derek Poon <derekp@xxxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux