pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Whats happen here?
4+ messages / 4 participants
[nested] [flat]

* Whats happen here?
@ 1999-12-06 15:13 Nikolay Mijaylov <nmmm@webfactory.bg>
  0 siblings, 0 replies; 4+ messages in thread

From: Nikolay Mijaylov @ 1999-12-06 15:13 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgreSQL.org>; pgsql-sql; pgsql-error@postgreSQL.org

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





--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Fw: Whats happen here?
@ 1999-12-06 15:17 Nikolay Mijaylov <nmmm@nmmm.nu>
  1999-12-07 14:44 ` Re: [SQL] Fw: Whats happen here? jose soares <jose@sferacarta.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Nikolay Mijaylov @ 1999-12-06 15:17 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@postgreSQL.org>; pgsql-sql

> 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; <nmmm@nmmm.nu>
>
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Fw: Whats happen here?
  1999-12-06 15:17 Fw: Whats happen here? Nikolay Mijaylov <nmmm@nmmm.nu>
@ 1999-12-07 14:44 ` jose soares <jose@sferacarta.com>
  1999-12-07 17:57   ` Re: [SQL] Fw: Whats happen here? Stuart Rison <rison@biochemistry.ucl.ac.uk>
  0 siblings, 1 reply; 4+ messages in thread

From: jose soares @ 1999-12-07 14:44 UTC (permalink / raw)
  To: Nikolay Mijaylov <nmmm@nmmm.nu>; +Cc: pgsql-general <pgsql-general@postgresql.org>; pgsql-sql

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; <nmmm@nmmm.nu>
> >
> >
>
> ************

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Fw: Whats happen here?
  1999-12-06 15:17 Fw: Whats happen here? Nikolay Mijaylov <nmmm@nmmm.nu>
  1999-12-07 14:44 ` Re: [SQL] Fw: Whats happen here? jose soares <jose@sferacarta.com>
@ 1999-12-07 17:57   ` Stuart Rison <rison@biochemistry.ucl.ac.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Stuart Rison @ 1999-12-07 17:57 UTC (permalink / raw)
  To: jose soares <jose@sferacarta.com>; +Cc: Nikolay Mijaylov <nmmm@nmmm.nu>; pgsql-general <pgsql-general@postgreSQL.org>; pgsql-sql

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; <nmmm@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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-12-07 17:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-06 15:13 Whats happen here? Nikolay Mijaylov <nmmm@webfactory.bg>
1999-12-06 15:17 Fw: Whats happen here? Nikolay Mijaylov <nmmm@nmmm.nu>
1999-12-07 14:44 ` jose soares <jose@sferacarta.com>
1999-12-07 17:57   ` Stuart Rison <rison@biochemistry.ucl.ac.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox