pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhats 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