Pivot / Crosstab PostgreSQL ERROR: invalid return type

Question

Hello I have created a view, but I want to pivot it with dynamic years.

Output before pivoting:

Expected output:

My query :

SELECT *
FROM crosstab(
'   select b.jenisiuran,
    date_part(''year''::text, a.insertdate) AS tahun,
    sum(b.jumlah_amt) AS jumlah
    FROM blm_dpembayaraniuran a
    JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
    GROUP BY date_part(''year''::text, a.insertdate), b.jenisiuran'  
) AS (TRANSAKSI TEXT, "2019" NUMERIC, "2020" NUMERIC, "2021" numeric);

and I'm getting error like this :

ERROR: invalid return type
Detail: SQL rowid datatype does not match return rowid datatype.

Thanks for helping me

Answer 1

I find using filtered aggregation easier to work with than crosstab()

select b.jenisiuran as transaksi,
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2019) as "2019",
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2020) as "2020",
       sum(b.jumlah_amt) filter (where extract(year from a.insertdate) = 2021) as "2021",
       sum(b.jumlah_amt) as total
FROM blm_dpembayaraniuran a
  JOIN blm_dpembayaraniuranline b ON a.blm_dpembayaraniuran_key::text = b.blm_dpembayaraniuran_key::text
WHERE a.insertdate >= date '2019-01-01' 
  AND a.insertdate <  date '2022-01-01'
GROUP b.jenisiuran;

Adding a range condition on inserdate should improve performance as the grouping only needs to be done for the rows in the desired range, not on all rows in the both tables.