I've seen in this github issue and this forum post that some users have successfully managed to use postgres' crosstab function, however, when I try to use it, I see the following error:
ERROR: function crosstab(unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 14
SELECT * FROM crosstab('
SELECT
EXTRACT(YEAR FROM m.created_at) || ''-'' || EXTRACT(MONTH FROM m.created_at) month_id,
o.display_name org_name,
count(DISTINCT m.user_id) mau
FROM mood m
JOIN "user" u ON m.user_id = u.id
JOIN organization o ON u.organization_id = o.id
JOIN (
SELECT
sub.org_id
FROM (
SELECT
EXTRACT(YEAR FROM m.created_at) || ''-'' || EXTRACT(MONTH FROM m.created_at) month_id,
o.id org_id,
count(DISTINCT m.user_id) mau
FROM mood m
JOIN "user" u ON m.user_id = u.id
JOIN organization o ON u.organization_id = o.id
WHERE o.slack_id NOT ILIKE ''T01AU0YAGJF''
GROUP BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at),
o.id
ORDER BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at)
) sub
GROUP BY sub.org_id
HAVING max(sub.mau) > 2
) org_ids ON org_ids.org_id = o.id
GROUP BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at),
o.id
ORDER BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at)
',
'
SELECT
sub.org_name
FROM (
SELECT
EXTRACT(YEAR FROM m.created_at) || ''-'' || EXTRACT(MONTH FROM m.created_at) month_id,
o.display_name org_name,
count(DISTINCT m.user_id) mau
FROM mood m
JOIN "user" u ON m.user_id = u.id
JOIN organization o ON u.organization_id = o.id
WHERE o.slack_id NOT ILIKE ''T01AU0YAGJF''
GROUP BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at),
o.id
ORDER BY
EXTRACT(YEAR FROM m.created_at),
EXTRACT(MONTH FROM m.created_at)
) sub
GROUP BY sub.org_name
HAVING max(sub.mau) > 2
ORDER BY sub.org_name
'
) AS ct (
"month_id" TEXT,
"our-client-name-1" bigint,
"our-client-name-2" bigint,
"our-client-name-3" bigint,
"our-client-name-4" bigint
-- there are more clients here, with their actual names, which I have edited for privacy
)
I tried using $$ instead of single quotes around the crosstab arguments but that made no difference. I also tried explicitly casting them to text ::text but then I just got the same error but with crosstab(text, text) does not exist. I tried calling tablefunc.crosstab() but got schema "tablefunc" does not exist.
This is a screenshot of the same query working correctly in DBeaver, my database client.
The database I am using is hosted by supabase and I turned the tablefunc extension on this morning to allow me to use the crosstab function. It's working in my client, so I don't understand why it doesn't work in metabase.
Please could someone advise? I assume that there's some issue with my query that's the actual root cause but the error is pretty frustrating; I know the function is available on the database I'm querying. The only difference I can think of between my db client and metabase environments is that my local client is connected via a role that has full access and metabase is connected via one that is readonly, but I don't see how that would make a difference seeing as it's a select statement.
