Re: merge-join for domain with underlying type text

On Sat, 2003-05-10 at 19:40, Randall Lucas wrote:
> -- Hmm, here's some code that manifests the problem:

Seems it's been fixed in 7.4.  In the mean time, you might try this:

select * from textual_test_1 full outer join textual_test_2 on
CAST(textual_test_1.key AS text) = CAST(textual_test_2.other_key AS
text);

-- LOG --
h=# create domain textual_test as text;
CREATE DOMAIN
h=# create table textual_test_1 (
h(#  key textual_test,
h(#  stuff varchar
h(# );
CREATE TABLE
h=# insert into textual_test_1 values ('alpha', 'asdflkjasdf');
INSERT 154456 1
h=# insert into textual_test_1 values ('bravo', 'asdfdasfsaff');
INSERT 154457 1
h=# insert into textual_test_1 values ('delta', 'asdfasfdas');
INSERT 154458 1
h=# create table textual_test_2 (
h(#  other_key textual_test,
h(#  more_stuff varchar
h(# );
CREATE TABLE
h=# insert into textual_test_2 values ('charlie', 'asdfasfasfda');
INSERT 154464 1
h=# insert into textual_test_2 values ('delta', 'asgasgdda');
INSERT 154465 1
h=# insert into textual_test_2 values ('echo', 'asdasfsdfsfda');
INSERT 154466 1
h=# select * from textual_test_1 full outer join textual_test_2 on
textual_test_1.key = textual_test_2.other_key; key  |    stuff     | other_key |  more_stuff
-------+--------------+-----------+---------------alpha | asdflkjasdf  |           |bravo | asdfdasfsaff |           |
   |              | charlie   | asdfasfasfdadelta | asdfasfdas   | delta     | asgasgdda      |              | echo
| asdasfsdfsfda 
(5 rows)
h=# select version();                               version
------------------------------------------------------------------------PostgreSQL 7.4devel on i386-unknown-freebsd4.8,
compiledby GCC 2.95.4 
(1 row)

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc