I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries:

    HOST   VOLUME     
    -----  ---------- 
    host1  vol1
    host1  vol2
    host1  vol3
    host2  bkvol1_DR
    host2  bkvol2_DR
    host2  bkvol3_DR

I'm trying to create a query which will give me the following:

   -------  -------  ------    ------
   host1    host2    vol1      bkvol1_DR
   host1    host2    vol2      bkvol2_DR
   host1    host2    vol3      bkvol3_DR

I've tried various incantations of substring(), trim(), and join clauses, and I'm getting nowhere. This is on PostgreSQL 9.2. In non-functional code, this is what I want:

select a.host, b.host, a.volume, b.volume from table as a, table as b 
    where a.volume is a substring of b.volume
  • How do you know that host2 is the destination host for host1? I don't see how you can make that connection. – a_horse_with_no_name Aug 18 '14 at 14:31
  • The "bkvol1_DR" volume exists only on host "host2", and is the target of "vol1" because the name "vol1" appears in the volume string. – John Aug 18 '14 at 14:42
select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on b.volume like '%'||a.volume||'%' 
   and a.host <> b.host;

Instead of LIKE you can also use position which might be easier to read:

select a.host, b.host, b.volume
from hosts as a
  join hosts as b 
    on a.host <> b.host
   and position(a.volume in b.volume) > 0 

SQLFiddle: http://sqlfiddle.com/#!15/a67c5/1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.