Re: merge-join for domain with underlying type text - Mailing list pgsql-sql

-- Hmm, here's some code that manifests the problem:

-- using text itself:
create table text_test_1 ( key text, stuff varchar
);
insert into text_test_1 values ('alpha', 'asdflkjasdf');
insert into text_test_1 values ('bravo', 'asdfdasfsaff');
insert into text_test_1 values ('delta', 'asdfasfdas');
create table text_test_2 ( other_key text, more_stuff varchar
);
insert into text_test_2 values ('charlie', 'asdfasfasfda');
insert into text_test_2 values ('delta', 'asgasgdda');
insert into text_test_2 values ('echo', 'asdasfsdfsfda');
select * from text_test_1 full outer join text_test_2 on  
text_test_1.key = text_test_2.other_key;

-- Works OK, BUT:

-- using domain textual:
create domain textual_test as text;
create table textual_test_1 ( key textual_test, stuff varchar
);
insert into textual_test_1 values ('alpha', 'asdflkjasdf');
insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
insert into textual_test_1 values ('delta', 'asdfasfdas');
create table textual_test_2 ( other_key textual_test, more_stuff varchar
);
insert into textual_test_2 values ('charlie', 'asdfasfasfda');
insert into textual_test_2 values ('delta', 'asgasgdda');
insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
select * from textual_test_1 full outer join textual_test_2 on  
textual_test_1.key = textual_test_2.other_key;

-- Will give ERROR:  FULL JOIN is only supported with mergejoinable  
join conditions

-- clean up
drop table text_test_1;
drop table text_test_2;
drop table textual_test_1;
drop table textual_test_2;
drop domain textual_test;

-- Best,
--
-- Randall

On Saturday, May 10, 2003, at 06:13 PM, Rod Taylor wrote:

> On Sat, 2003-05-10 at 17:04, Randall Lucas wrote:
>> Hi Folks,
>>
>> If I have a domain defined with an underlying type of "text" or
>> "varchar," and I need to FULL OUTER JOIN two tables based upon the
>> similarity in these fields, I am getting:
>
> Seems it works perfectly fine with integers and text based domains on
> 7.3 and 7.4.  Do you have a full failing example?
>
> junk=# create domain int as integer;
> CREATE DOMAIN
> junk=# create table a (col1 int);
> CREATE TABLE
> junk=# create table b (col1 int);
> CREATE TABLE
> junk=# select * from a full outer join b using (col1);
>  col1
> ------
> (0 rows)
>
> junk=# select version();
>                                 version
> ----------------------------------------------------------------------- 
> -
>  PostgreSQL 7.4devel on i386-unknown-freebsd4.8, compiled by GCC 2.95.4
> (1 row)
>
> -- 
> Rod Taylor <rbt@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
> <signature.asc>