Question 252123 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Joining two tables with pattern matching

I am new to joining two tables in PostgreSQL.

Table 1: O_REMEDY_PEOPLEGROUP

manager_name manager_hierarchy
farhanpat raju jacob watson
soumyvant neha william
amitk piyush shawn
brian david warner

Table 2: Org_details

org_name vp_details
org1 jacob
org2 piyush
org3 neha

We want to search for each VP if it’s present in the manager hierarchy of table 1.
If present, third column should report org_name, else null.

Output Table:

manager_name manager_hierarchy org_name
farhanpat raju jacob watson org1
soumyvant neha william org3
amitk piyush shawn org2
brian david warner null
Tags:

2

Answers


  1. 0

    As already pointed data stored as comma separated list will bring more and more troubles.

    See Is storing a delimited list in a database column really that bad?

    As per the question.

    In your particular case you have stored not only a comma separated string but [ ,] symbols and a space between ‘words’.

    So FIND_IN_SET cant be used without doing some replace.

    The following query replace the above symbols and the space with empty.

    Try

    select orp.manager_name,
       orp.manager_hierarchy,
       od.org_name
from O_REMEDY_PEOPLEGROUP orp
left join Org_details od on  FIND_IN_SET(od.VPdetails, replace(replace(replace(orp.manager_hierarchy,']','') ,'[',''),' ',''));

    Another options is using LIKE

    select orp.manager_name,
       orp.manager_hierarchy,
       od.org_name
from O_REMEDY_PEOPLEGROUP orp
left join Org_details od on replace(replace(replace(orp.manager_hierarchy,']','') ,'[',''),' ','')
LIKE CONCAT('%', od.VPdetails , '%')

    See example

    Note. No index will be used in either query, so performance for large data set will be terrible

    Login or Signup to reply.
  2. 0

    While stuck on your unfortunate design with multiple values concatenated as single string, this should work as efficiently as it gets (while being accurate):

    SELECT r.manager_name, r.manager_hierarchy
    , (SELECT d.org_name
       FROM   unnest(string_to_array(r.manager_hierarchy, ' ')) vp_details
       JOIN   org_details d USING (vp_details)
       LIMIT  1) AS org_name
FROM   o_remedy_peoplegroup r;

    fiddle

    Since it’s undefined in the question I pick the first arbitrary match with LIMIT 1. (There might be multiple.)

    If org_details isn’t trivially small, there should be an index on (vp_details) to get index scans, or even on (vp_details, org_name) to get index-only scans.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search