Thread: Sorry..

Ignore previous half-completed email.

How do you get this to work in 7.2.1?

I'm creating a view of a table, but I'm trying to do something like
this:


create table t ( id integer not null, amount numeric(7,2)
);

create view v as select id as v_id,   'paid amount: ' || amount as v_comment from t
;

You get this:

ERROR:  Unable to identify an operator '||' for types 'unknown' and
'numeric'       You will have to retype this query using an explicit cast

None of these work:

CAST(amount AS text)
CAST(amount AS varchar)
CAST(amount AS char)
and this:
CAST(amount AS real) works, but if amount is 12.00, then you just get '12' -
which is not cool.

Any ideas?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> [ there's no cast from numeric to text ]

Feel free to contribute one.

In the bad old days when we couldn't distinguish explicit from implicit
cast functions, I was wary of adding new cast pathways.  Too many
implicit casts and you have no type system at all.  But in 7.3 there
should be no reason to object to an explicit-only cast from numeric
to text or vice versa.
        regards, tom lane

Christopher,

> In the bad old days when we couldn't distinguish explicit from implicit
> cast functions, I was wary of adding new cast pathways.  Too many
> implicit casts and you have no type system at all.  But in 7.3 there
> should be no reason to object to an explicit-only cast from numeric
> to text or vice versa.

I'd suggest making the explicit cast of numeric to text be the exact
equivalent of:

SELECT btrim(to_char(numeric, '999,999,999,999.9999999999'))
or similar.

--
-Josh BerkusAglio Database SolutionsSan Francisco

Josh Berkus wrote:
> Christopher,
> 
> 
>>In the bad old days when we couldn't distinguish explicit from implicit
>>cast functions, I was wary of adding new cast pathways.  Too many
>>implicit casts and you have no type system at all.  But in 7.3 there
>>should be no reason to object to an explicit-only cast from numeric
>>to text or vice versa.
> 
> 
> I'd suggest making the explicit cast of numeric to text be the exact 
> equivalent of:
> 
> SELECT btrim(to_char(numeric, '999,999,999,999.9999999999'))
> or similar.
> 


In this vain, is there someplace in the docs that has a type conversion 
table (matrix) that shows what datatype can be cast into what other 
datatype (both implicitly and explicitly)?  I haven't seen one and it 
would be helpful for us newbies.

Thanks!
Kevin

Kevin,

> In this vain, is there someplace in the docs that has a type conversion
> table (matrix) that shows what datatype can be cast into what other
> datatype (both implicitly and explicitly)?  I haven't seen one and it
> would be helpful for us newbies.

I don't think it exists.   Hey, how about a volunteer to write one?

--
-Josh BerkusAglio Database SolutionsSan Francisco

On Tue, Jul 16, 2002 at 12:37:04PM -0500, Kevin Brannen wrote:
> Josh Berkus wrote:
> > Christopher,
> > 
> > 
> >>In the bad old days when we couldn't distinguish explicit from implicit
> >>cast functions, I was wary of adding new cast pathways.  Too many
> >>implicit casts and you have no type system at all.  But in 7.3 there
> >>should be no reason to object to an explicit-only cast from numeric
> >>to text or vice versa.
> > 
> > 
> > I'd suggest making the explicit cast of numeric to text be the exact 
> > equivalent of:
> > 
> > SELECT btrim(to_char(numeric, '999,999,999,999.9999999999'))
> > or similar.
Note: the btrim() call is needless. You can use Fill-Mode:
SELECT to_char(numeric, 'FM999,999,999,999.9999999999');                         ^^^It's in docs :-)
   Karel

-- Karel Zak  <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz