Thread: Problems with '||' concatenation operator.

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=>

tried casting one as the other:

cgh=> select chromosome::bpchar || 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

or

cgh=> select chromosome || arm::varchar 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=>

tried converting both to text (in desperation!):

cgh=> select chromosome::text || arm::text 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=>

Any suggestions on how to get the concatenation to work?
Also, I'm hoping to eventually concatenate an int4 field to the other two
so, having solved the above problem, how would I tackle that one?

Regards,

S.



+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

>Beth Strohmayer wrote
>
>>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=>
>
>Try select chromosome || varchar(arm) as locus from experiment;
>

Doesn't work for me,

cgh=> select chromosome || varchar(arm) as locus from experiments;
ERROR:  parser: Syntax error at or near "arm"

neither does select chromosome || arm::varchar as locus from experiment;

but the solution is clearly in some form of casting!

I'm using PG 6.4, perhaps it's a version thing?

regards,

S.






+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

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