Thread: char/varchar conversions

I'm having trouble getting Postgres 6.5.3 to do what I want. The problem seems
to be fixed in version 7, but I could really do with a workaround for 6.5.3,
since I'm stuck with that for the time being.

journals2=> create table vc(v varchar(9), c char(9));
CREATE
journals2=> insert into vc values('abc','abc');
INSERT 1631143 1
journals2=> select * from vc where v::char(9)=c;
ERROR:    Unable to identify an operator '=' for types 'varchar' and 'bpchar'
    You will have to retype this query using an explicit cast

Surely I did use an explicit cast? The same thing happens whether I cast one
side or the other or both. Let's try being sneakier:

journals2=> select * from vc where v::text::varchar=c::text::varchar;
pqReadData() -- backend closed the channel unexpectedly.
    This probably means the backend terminated abnormally
    before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible.  Terminating.

That's obviously too sneaky.

--
    Peter Haworth    pmh@edison.ioppublishing.com
Anything that can go wrong, will go wr

Peter Haworth <pmh@edison.ioppublishing.com> writes:
> I'm having trouble getting Postgres 6.5.3 to do what I want. The
> problem seems to be fixed in version 7, but I could really do with a
> workaround for 6.5.3, since I'm stuck with that for the time being.

> journals2=> select * from vc where v::char(9)=c;
> ERROR:    Unable to identify an operator '=' for types 'varchar' and 'bpchar'
>     You will have to retype this query using an explicit cast

I think you need to upgrade to 7.0, or else re-make your table so that
you don't need to compare char and varchar.  Broken handling of casts
between binary-compatible types is one of the 6.5 shortcomings that
we fixed in 7.0.

I suppose if you were really desperate, this would work:

create function equal(bpchar,bpchar) returns bool as
'select $1 = $2' language 'sql';

and then
    SELECT ... WHERE equal(v,c);

but performance would be horrid.

            regards, tom lane

On Wed, 15 Nov 2000 11:19:40 -0500, Tom Lane said:
> Peter Haworth <pmh@edison.ioppublishing.com> writes:
>  > I'm having trouble getting Postgres 6.5.3 to do what I want. The
>  > problem seems to be fixed in version 7, but I could really do with a
>  > workaround for 6.5.3, since I'm stuck with that for the time being.
>
>  > journals2=> select * from vc where v::char(9)=c;
>  > ERROR:    Unable to identify an operator '=' for types 'varchar' and 'bpchar'
>  >     You will have to retype this query using an explicit cast
>  I suppose if you were really desperate, this would work:
>
>  create function equal(bpchar,bpchar) returns bool as
>  'select $1 = $2' language 'sql';
>
>  and then
>      SELECT ... WHERE equal(v,c);
>
>  but performance would be horrid.

No kidding. My actual comparison is a join between two tables. This function
compares pretty badly with my current workaround of adding another join
through a table with char and varchar versions of the field in question, which
I thought was going to be really bad. (Fortunately, the range of values is
pretty small and static).

I've got different types because one table describes journals - the field is
char(9) for ISSNs, and the other describes subscriptions - the field is
varchar(9) to hold ISSNs and packages, which all have IDs less than 9
characters.

>  I think you need to upgrade to 7.0

I agree, and I'm sure it'll happen eventually...

--
    Peter Haworth    pmh@edison.ioppublishing.com
"Perhaps I'm missing the gene for making enemies." -- Larry Wall