If I have comma seperated values in a column , to use it in where condition I have used split_part. But it is taking more time in pgAdmin 4

Question

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?

Answer 1

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 || ',%';