Обсуждение: 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