If I have comma seperated values in a column , to use it in where condition I have used split

In PostgreSQL, I have a column receipt_id which will have comma separated values or a single value . Eg:

I need to use the values in the third column with another table called Voucher in where condition .

I have used split_part.

select  ap.document_no AS invoice_number,
    ap.curr_date AS invoice_date,ap.receipt_id,split_part(ap.receipt_id::text, ','::text, 1),
    split_part(ap.receipt_id::text, ','::text, 2)
    from ap_invoice_creation ap , voucher v 
    where  (v.voucher_id::text IN      
           ( SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 1) AS parts
           FROM ap_invoice_creation
          WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text
        UNION
         SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 2) AS parts
           FROM ap_invoice_creation
          WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text
        UNION
         SELECT split_part(ap_invoice_creation.receipt_id::text, ','::text, 3) AS parts
           FROM ap_invoice_creation
          WHERE ap_invoice_creation.receipt_id::text = ap.receipt_id::text)) AND ap.status::text = 'Posted'::text

But this is a part of query, it is taking more time. Because of this entire query is taking more time.

Is there any other way to handle this?

CodePudding user response：

Ideally, you should not even be storing CSV like this. That being said, there is no need for SPLIT_PART() here and big ugly union. Consider this version:

SELECT
    ap.document_no AS invoice_number,
    ap.curr_date AS invoice_date,
    ap.receipt_id,
    SPLIT_PART(ap.receipt_id::text, ',', 1),
    SPLIT_PART(ap.receipt_id::text, ',', 2)
FROM ap_invoice_creation ap
INNER JOIN voucher v 
    ON ',' || ap.receipt_id || ',' LIKE '%,' || v.voucher_id::text || ',%';

CodePudding user response：

One way to improve performance would be to get rid of the dreaded CSV value, and store the data in a proper one-to-many relationship.

If you can't do that, you can use an EXISTS condition rather than a cross join with an IN condition that uses three queries:

select ap.document_no AS invoice_number,
       ap.curr_date AS invoice_date,
       ap.receipt_id,
       split_part(ap.receipt_id, ',', 1),
       split_part(ap.receipt_id, ',', 2)
from ap_invoice_creation ap 
WHERE EXISTS (select *
              from voucher v
              where v.voucher_id = any (string_to_array(ap.receipt_id, ',')))
WHERE ap.status = 'Posted'