Try this:
create table interface ( mac macaddr, primary key(mac) );
insert into interface (mac) values ('00:11:22:33:44:55');
insert into interface (mac) values ('00:11:22:33:44:5a');
insert into interface (mac) values ('00:11:22:33:44:5f');
create table host (
mac macaddr,
ip inet,
foreign key (mac) references interface,
primary key (ip)
);
insert into host ( mac, ip ) values ('00:11:22:33:44:55', '192.168.1.1');
insert into host ( mac, ip ) values (null, '192.168.1.2');
select * from interface full join host using (mac);
ERROR: FULL JOIN is only supported with mergejoinable join conditions
If you replace the "macaddr" columns with "varchar(17)":
mac | ip
-------------------+-------------
00:11:22:33:44:55 | 192.168.1.1
00:11:22:33:44:5a |
00:11:22:33:44:5f |
| 192.168.1.2
Why? This seems like a bug. Running on postgresql 7.1.3, compiled from the
source RPM on the download site. If people could CC any replies to my email,
I'd be grateful.
--
Regards,
Phil
+------------------------------------------+
| Phil Mayers |
| Network & Infrastructure Group |
| Information & Communication Technologies |
| Imperial College |
+------------------------------------------+