Crosstab query with CTE returns error: relation "cte" does not exist

Question

I have two schemas on my PostgreSQL server, and I'm trying to create a pivoted table:

WITH cte AS (
   SELECT l.fcid, t.fixture_code, COUNT(f.fixture_type_id) AS count  
        FROM aim.locations as l
            JOIN aim.fixture_instances as f
            ON l.id = f.location_id
            JOIN aim.fixture_types as t
            ON f.fixture_type_id = t.id
   GROUP BY l.fcid, t.fixture_code
   )
SELECT *
FROM lab.CROSSTAB('SELECT fcid, fixture_code, SUM(count) FROM cte group by 1,2 order by 1,2', 
                   'Select DISTINCT fixture_code from cte order by 1') 
                AS CT (FCID integer, "fx1" bigint, "fx2" bigint)
ORDER BY fcid;

However, I receive an error:

ERROR:  relation "cte" does not exist
LINE 1: Select DISTINCT fixture_code from cte order by 1

The query worked when I had only one schema.
I use a CTE so I could create a view with the query.
What's wrong here?

Put the CTE definition before the queries you pass to the function, if you want to access it in these queries. — sticky bit, May 08 '21 at 21:28
@stickybit not sure what you mean. Isn't it already like you described. Will it be possible to show? thank you! — Vitalii M, May 08 '21 at 22:51
No it isn't as I described. Put the CTE **in** the arguments of `crosstab()` **not** the query selecting **from** `crosstab()` -- `... crosstab('WITH ... SELECT ...', 'WITH ... SELECT ...', ...` — sticky bit, May 08 '21 at 22:53

Erwin Brandstetter · Accepted Answer · 2021-05-09T02:46:44.327

In fact, you don't need the outer SELECT of the query string argument at all. The added sum(count) changes nothing in this constellation. The query in the CTE already delivers the end result to be fed to crosstab(). Get rid of the CTE altogether - thereby also fixing the immediate cause of the error:

SELECT *
FROM   lab.crosstab(
         $$
         SELECT l.fcid, t.fixture_code, count(*) AS cnt
         FROM   aim.locations         l
         JOIN   aim.fixture_instances f ON l.id = f.location_id
         JOIN   aim.fixture_types     t ON f.fixture_type_id = t.id
         GROUP  BY 1,2
         ORDER  BY 1,2
         $$
      ,  $$VALUES ('fx1'), ('fx2')$$
       ) AS ct(fcid int, fx1 bigint, fx2 bigint);

Some other things:

There is no point in running SELECT DISTINCT when you have to spell out corresponding columns names in the column definition list anyway. Replaced it with a plain VALUES expression.

f.fixture_type_id is NOT NULL by definition of the query, so use the faster equivalent count(*).

Basics here:

PostgreSQL Crosstab Query

Crosstab query with CTE returns error: relation "cte" does not exist

1 Answers1