PostgreSQL Crosstab: Month rows and Day columns; Error rowid datatype does not match return rowid datatype

Question

I am trying to create a crosstab table that will have rows = months and columns = days (ie 1, 2, 3, 4...31).

    Month |   1  |   2  |  3  |  4  |  5  |  6  |  7  |  8  |  9  |  10  | 11  |  12 ...
    ------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
     9    | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
     8    | 1000 |      |     |     |     |     |     |     |     |     |      |

My query is as follows:

SELECT * FROM crosstab(
    $$
    SELECT
      extract(month from created_at) AS themonth,
      extract(day from created_at) AS theday,
      COUNT(*)
    FROM public.users
    WHERE created_at >= Now() - Interval '90 Days' AND created_at < Now() - Interval '1 days'
    GROUP BY created_at
    ORDER BY 1,2
  $$
) AS final_result (
  themonth int,
    theday int
)

The below receives an error: rowid datatype does not match return rowid datatype

This is my first time using crosstab.

I have a feeling this is a simple fix and would appreciate any help. Thanks!

You're selecting 3 columns while final_result only has two? Of course, just noticed your alias. — Gerard H. Pille, Sep 24 '18 at 20:28

score 6 · Accepted Answer · answered Sep 24 '18 at 21:15

There are two issues. The row declaration in final_result must exactly match tuples returned by the function. Also, you should use the variant of the function with two parameters crosstab(text source_sql, text category_sql).

Example for 5 days:

SELECT * FROM crosstab(
    $$
    SELECT
      extract(month from created_at) AS themonth,
      extract(day from created_at) AS theday,
      COUNT(*)
    FROM public.users
    WHERE created_at >= Now() - Interval '90 Days' AND created_at < Now() - Interval '1 days' AND alternate_email not like '%@flyhomes.com%'
    GROUP BY created_at
    ORDER BY 1,2
    $$,
    $$
        SELECT generate_series(1, 5) -- should be (1, 31)
    $$
) AS final_result (
  themonth float, "1" bigint, "2" bigint, "3" bigint, "4" bigint, "5" bigint -- should be up to "31"
)

Working example in rextester.

Thanks, this perfectly solved it. I think sadly I tried this before and had an extraneous "," somewhere. Thanks for helping me stop banging my head against the wall. — Dys_Lexi_A, Sep 24 '18 at 21:22

PostgreSQL Crosstab: Month rows and Day columns; Error rowid datatype does not match return rowid datatype

1 Answers1