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

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 – Postgresql

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

enter image description here

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?

Tags:

2

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    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 || ',%';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search