PostgreSQL and Crosstab(): ERROR: return and sql tuple descriptions are incompatible

Question

I am running the following basic SQL query:

SELECT
  a.project_name,
  CASE
    WHEN to_char(date_trunc('month', u.billing_period_start), 'MM-YYYY') = '07-2016' THEN 'July_2016'
    WHEN to_char(date_trunc('month', u.billing_period_start), 'MM-YYYY') = '08-2016' THEN 'August_2016'
    WHEN to_char(date_trunc('month', u.billing_period_start), 'MM-YYYY') = '09-2016' THEN 'September_2016'
  END AS billing_month,
  u.unique_visitor_count AS visitors
FROM table_1 u,
     (SELECT
       a.account_id AS project_id,
       a.project_name
     FROM table_2 a) a
WHERE u.project_id = a.project_id
AND date_trunc('month', u.billing_period_start) BETWEEN '2016-07-01' AND '2016-09-01'
ORDER BY 1, 2;

This query gets me the following result set: project_name | month_name | visitors -------------|----------------|--------- name_1 | August_2016 | 0 name_1 | July_2016 | 0 name_1 | September_2016 | 0 name_2 | August_2016 | 0 name_2 | July_2016 | 0 name_2 | September_2016 | 0 name_3 | August_2016 | 39 name_3 | July_2016 | 68 name_3 | September_2016 | 25 name_4 | August_2016 | 536914 name_4 | July_2016 | 16142 name_4 | September_2016 | 487117

I am trying to use the following crosstab() query:

SELECT * FROM crosstab
('SELECT 
    a.project_name,
    case when to_char(date_trunc(''month'', u.billing_period_start),''MM-YYYY'')=''07-2016'' then ''July_2016''
         when to_char(date_trunc(''month'', u.billing_period_start),''MM-YYYY'')=''08-2016'' then ''August_2016''
         when to_char(date_trunc(''month'', u.billing_period_start),''MM-YYYY'')=''09-2016'' then ''September_2016'' end as billing_month,
    u.unique_visitor_count as visitors
FROM table_1 u,
     (select a.account_id as project_id, a.project_name
      from table_2 a) a
where u.project_id = a.project_id
and date_trunc(''month'', u.billing_period_start) between ''2016-07-01'' and ''2016-09-01''
order by 1,2')
AS (project_name TEXT, July_2016 INTEGER, August_2016 INTEGER, September_2016 INTEGER);

I want to turn the first result set above into the following: project_name | July_2016 | August_2016 | September_2016 ------------|-----------|-------------|--------------- name_1 | 0 | 0 | 0 name_2 | 0 | 0 | 0 name_3 | 68 | 39 | 25 name_4 | 16142 | 536914 | 487117

However I am receiving the error:

ERROR: return and sql tuple descriptions are incompatible

I will never have any null or missing results for visitors. I have read previous posts about the second parameter for crosstab() and I don't believe I need it here. I have tried changing the INTEGER columns in my crosstab() to NUMERIC, but I still receive the same error.

What am I doing wrong? Could someone provide a modified, working crosstab() query to show me a fix?

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-07-11T04:47:28

I would just use conditional aggregation:

SELECT a.project_name,
       SUM(case when date_trunc('month', u.billing_period_start) = '2016-07-01'
                then u.unique_visitor_count else 0
           end) as uv_201607,
       SUM(case when date_trunc('month', u.billing_period_start) = '2016-08-01' 
                then u.unique_visitor_count else 0
           end) as uv_201608,
       SUM(case when date_trunc('month', u.billing_period_start) = '2016-09-01' 
                then u.unique_visitor_count else 0
           end) as uv_201609
FROM table_1 u JOIN
     table_2 a
     ON u.project_id = a.project_id
WHERE  date_trunc('month', u.billing_period_start) BETWEEN '2016-07-01' AND '2016-09-01'
GROUP BY a.project_name
ORDER BY a.project_name;

Notes:

There is no need to use to_char() on date_trunc().
Use standard formats for dates -- YYYY-MM-DD.
The subquery in the FROM clause is unnecessary.
Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

PostgreSQL and Crosstab(): ERROR: return and sql tuple descriptions are incompatible

1 Answers