Received: from mail-b.bcc.ac.uk (mail-b.bcc.ac.uk [144.82.100.22]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA72077; Tue, 7 Dec 1999 12:49:13 -0500 (EST) (envelope-from rison@biochemistry.ucl.ac.uk) Received: from biochem.ucl.ac.uk (actually host bsmori1.biochem.ucl.ac.uk) by mail-b.bcc.ac.uk with SMTP (XT-PP) with ESMTP; Tue, 7 Dec 1999 17:48:46 +0000 Received: from bsmlx17.biochem.ucl.ac.uk (bsmlx17 [128.40.46.142]) by biochem.ucl.ac.uk (8.9.2/8.9.1) with ESMTP id RAA06694; Tue, 7 Dec 1999 17:48:59 GMT Received: from localhost (rison@localhost) by bsmlx17.biochem.ucl.ac.uk (8.9.3/8.6.6) with ESMTP id RAA01672; Tue, 7 Dec 1999 17:57:30 GMT X-Authentication-Warning: bsmlx17.biochem.ucl.ac.uk: rison owned process doing -bs Date: Tue, 7 Dec 1999 17:57:30 +0000 (GMT) From: Stuart Rison X-Sender: rison@bsmlx17 To: jose soares cc: Nikolay Mijaylov , pgsql-general , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Fw: Whats happen here? In-Reply-To: <384D1D3B.2BF89CBB@sferacarta.com> Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Hum, I wonder if it's necessary to create an operator? I had a similar problem a while back, the failing operator was || the concatenation operator, for exactly the same reason. This is the answer I got from Ross Reedstrom (reedstrm@rice.edu) and it worked a treat: "> 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)" This was PG 6.4 and perhaps things have changed but at least here you have a 'generalised' solution for all operator between a bpchar and a varchar. HTH, Stuart. On Tue, 7 Dec 1999, jose soares wrote: > Try this: > > drop function char_eq_varchar(bpchar,varchar); > create function char_eq_varchar(bpchar,varchar) returns bool as > ' > declare > i2 text; > i1 text; > begin > i1:= trim($1); > i2:= $2; > if i1 = i2 then > return TRUE; > else > return FALSE; > end if; > end; > ' language 'plpgsql'; > > > drop operator = (bpchar,"varchar"); > create operator = ( > leftarg=bpchar, > rightarg="varchar", > procedure=char_eq_varchar, > commutator='=', > negator='<>', > restrict=eqsel, > join=eqjoinsel > ); > > drop table x; > drop table y; > create table x(a char(20)); > create table y(a varchar(5)); > insert into x values('a'); > insert into y values('a'); > select * from x, y where x.a = y.a; > a |a > --------------------+- > a |a > (1 row) > > > Jose' > > > Nikolay Mijaylov ha scritto: > > > > Whats happen here? > > > ------------------------- > > > > > > root=> \dt > > > > > > Database = root > > > +------------------+----------------------------------+----------+ > > > | Owner | Relation | Type | > > > +------------------+----------------------------------+----------+ > > > | root | x | table | > > > | root | y | table | > > > +------------------+----------------------------------+----------+ > > > root=> \d x > > > > > > Table = x > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > | Field | Type | > > > Length| > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > | a | char() | > > > 20 | > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > root=> \d y > > > > > > Table = y > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > | Field | Type | > > > Length| > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > | a | varchar() | > > > 5 | > > > > > +----------------------------------+----------------------------------+----- > > > --+ > > > root=>select * from x a, y b where a.a = b.a; > > > ERROR: There is more than one possible operator '=' for types 'bpchar' > > and > > > 'varchar' > > > You will have to retype this query using an explicit cast > > > > > > > > > > > > root=> select * from x a, y b where text(a.a) = text(b.b); > > >ERROR: There is more than one possible operator '=' for types 'bpchar' and > > 'varchar' > > > You will have to retype this query using an explicit cast > > > > > > -------------------------------------------------------------- > > > The reboots are for hardware upgrades! > > > "http://www.nmmm.nu; > > > > > > > > > > ************ > Stuart C. G. Rison Department of Biochemistry and Molecular Biology 6th floor, Darwin Building, University College London (UCL) Gower Street, London, WC1E 6BT, United Kingdom Tel. 0207 504 2303, Fax. 0207 380 7193 e-mail: rison@biochem.ucl.ac.uk