Issue with pivot table query . I am using this in django as a raw query on a postgres database

Question

SELECT Name,'1','2','3'
FROM (
    SELECT
        database_jobdefect.defect_id,
        database_session.name,
        count(*) AS cu
    FROM
        database_session IN ner
        JOIN database_job ON database_job.session_id = database_session.id
        INNER JOIN database_jobdefect ON database_job.id = database_jobdefect.job_id
    GROUP BY
        database_session.name,
        database_jobdefect.defect_id) PIVOT (count(database_session.name)
        FOR database_jobdefect.defect_id IN ([1],[2],[3]))

The error i am getting is

"ProgrammingError: syntax error at or near "(" LINE 1: ...on.name,database_jobdefect.defect_id) PIVOT (count(database_..."

I loathe the syntax, but Postgres does have a crosstab function using the `tablefunc` extensions. https://stackoverflow.com/questions/3002499/postgresql-crosstab-query — Hambone, Mar 11 '20 at 11:57

score 1 · Answer 1 · answered Mar 11 '20 at 10:53

Postgres doesn't support pivot syntax. Just use conditional aggregation:

SELECT Name,
       COUNT(*) FILTER (WHERE djd.defect_id = 1) THEN defect_1,
       COUNT(*) FILTER (WHERE djd.defect_id = 2) THEN defect_2,
       COUNT(*) FILTER (WHERE djd.defect_id = 3) THEN defect_3
FROM database_session ds JOIN
     database_job dj 
     ON dj.session_id = ds.id JOIN
        database_jobdefect djd
     ON dj.id = djd.job_id 
GROUP BY ds.name;

Issue with pivot table query . I am using this in django as a raw query on a postgres database

1 Answers1