Stuart -
I think this is a consequence of the internal representations of text
and varchar and char being identical, so that the cast finctions think
there's nothing to do.
I think this is fixed in 6.5. A work around for 6.4 is to apply
afunction that does nothing to the text: I've used btrim() in the past
(since I usually want to get rid of trailing whitespace anyway:
test=> select btrim(chromosome) || btrim(arm) as locus from experiment;
locus
-----
22q
17p
(2 rows)
Ross
Stuart Rison wrote:
>
> Dear All,
>
> I'm trying to get the concatenation operator (||) to work with different
> character variables (i.e. varchar and bpchar)... and failing.
>
> consider the following:
>
> create table experiment (
> chromosome varchar(2), // that for chromosomes 1-22 and X and Y
> arm char(1) // can only be one of 'q' or 'p'
> );
>
> insert into experiment values ('22','q');
> insert into experiment values ('17','p');
>
> select * from experiment;
> chromosome|arm
> ----------+---
> 22|q
> 17|p
> (2 rows)
>
> I want to select a field as the concatenation of the chromosome and the arm...
>
> cgh=> select chromosome || arm as locus from experiment;
> ERROR: There is more than one possible operator '||' for types 'varchar'
> and 'b
> pchar'
> You will have to retype this query using an explicit cast
> cgh=>
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
