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

Postgresql – Postgres sql to compare data between two tables and populate in target

Scenario :
Input table 1 : need to compare the value of field2 of table1 with field 3 of table 2
example :
select from svc_lz.fn_abc;svc_lz.fn_cde; so field 2 values of table 1 is
present after ‘select’ i.e svc_lz.fn_abc and svc_lz.fn_cde so we need to
join with field 3 of table 2 example : svc_lz.fn_abc and svc_lz.fn_cde.If there is join
then fetch field 4 assigned to table 2 and place it in output table field 3.

Input table 1:

field1 field2
a select from svc_lz.fn_abc;svc_lz.fn_cde;
b select *From mfg_lz.fn_test,mfg_a.fn_test1
c select *From mfg_j.fn_test2
d select *From mfg_j.fn_test3;mfg_j.fn_test4;

Input table 2 :

field3 field4
svc_lz.fn_abc {test}
svc_lz.fn_cde {test1,test2}
mfg_lz.fn_test {test3}
mfg_j.fn_test4 {test5}

Output:

field1 field2 field3
a select from svc_lz.fn_abc;svc_lz.fn_cde; test,test1,test2
b select *From mfg_lz.fn_test,mfg_a.fn_test1 test3
c select *From mfg_j.fn_test2 NULL
d select *From mfg_j.fn_test3;mfg_j.fn_test4; test5

I have search and check need to do string_split and perform inner join between field 2 from tbl1 and
field3 from tbl2 but I am not sure how to start with,I have tried using cte
functionality but I am new to sql so struggling to find the starting point for this.If
anyone can help pls.

Tags:

2

Answers


  1. 0

    For solving your problem in PostgreSQL, where you need to compare data between two tables based on fields extracted from strings and then populate a target table, you can follow a step-by-step approach using PostgreSQL-specific functions.

    Steps to Solve the Problem

    1. Extract Values from field2 of table1: Use the regexp_split_to_table function to split field2 on specific delimiters and prepare it for joining.
    2. Join the Tables: Perform a join with table2 on these split values to find corresponding entries.
    3. Aggregate Results: Use string_agg to concatenate all matching field4 values.
    4. Insert into Target Table: Populate the target table with the results.

    SQL Query Explanation

    Step 1: Split field2 and Prepare for Join

    Here’s how you can create a Common Table Expression (CTE) to handle the splitting:

    WITH SplitField2 AS (
    SELECT
        field1,
        unnest(regexp_split_to_array(field2, '[,;]')) AS parsed_value
    FROM
        table1
)

    This CTE splits field2 on commas and semicolons and creates a new row for each part using unnest.

    Step 2: Join and Aggregate

    Now, use the CTE to join with table2 and aggregate the results:

    SELECT
    t1.field1,
    t1.field2,
    string_agg(t2.field4, ',') AS field3
FROM
    SplitField2 t1
JOIN
    table2 t2 ON t1.parsed_value = t2.field3
GROUP BY
    t1.field1, t1.field2

    Step 3: Insert into Target Table

    Assuming you have a target table with columns field1, field2, and field3, you can insert the results as follows:

    INSERT INTO target_table (field1, field2, field3)
SELECT
    t1.field1,
    t1.field2,
    string_agg(t2.field4, ',') AS field3
FROM
    SplitField2 t1
JOIN
    table2 t2 ON t1.parsed_value = t2.field3
GROUP BY
    t1.field1, t1.field2

    Additional Notes

    • Ensure that your regex in regexp_split_to_array accurately captures the delimiters you expect in field2.
    • string_agg is used to concatenate values from field4 associated with each match, separated by commas.
    • Adjust the join condition and string aggregation as needed based on your data’s format and consistency.

    This approach will effectively allow you to compare and aggregate data from two PostgreSQL tables and populate a target table based on your criteria.

    For more postgres/yugabyte exciting posts visit: https://www.developerscoffee.com

    Login or Signup to reply.
  2. 0

    See example

    select field1,field2,string_agg(distinct fld,',') field3
from(
select *
from(
select field1,field2,fld
from table1 t1
left join (
    select * from table2
    cross join 
       string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld
  )t2
  on t1.field2 like concat('%',t2.field3,'%')
)x
)b
group by field1,field2

    Result is

    field1 field2 field3
    a select from svc_lz.fn_abc;svc_lz.fn_cde; test,test1,test2
    b select *From mfg_lz.fn_test,mfg_a.fn_test1 test3
    c select *From mfg_j.fn_test2 null
    d select *From mfg_j.fn_test3;mfg_j.fn_test4; test5

    First part is

    select * from table2
cross join string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld

    This subquery output is

    field3 field4 fld
    svc_lz.fn_abc {test} test
    svc_lz.fn_cde {test1,test2} test1
    svc_lz.fn_cde {test1,test2} test2
    mfg_lz.fn_test {test3} test3
    mfg_j.fn_test4 {test5} test5

    Next step JOIN table1 and expanded table2 (previous subquery)

    select field1,field2,fld
from table1 t1
left join (
    select * from table2
    cross join 
       string_to_table(trim(replace(replace(field4,'{',''),'}','')),',') fld
  )t2
  on t1.field2 like concat('%',t2.field3,'%')

    Output is

    field1 field2 fld
    a select from svc_lz.fn_abc;svc_lz.fn_cde; test
    a select from svc_lz.fn_abc;svc_lz.fn_cde; test1
    a select from svc_lz.fn_abc;svc_lz.fn_cde; test2
    b select *From mfg_lz.fn_test,mfg_a.fn_test1 test3
    c select *From mfg_j.fn_test2 null
    d select *From mfg_j.fn_test3;mfg_j.fn_test4; test5

    Demo

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

Back To Top
Search