CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expressioncrosstab
Vitalii M
Vitalii M

Reputation: 15

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

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?

Upvotes: 0

Views: 951

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656481

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:

Upvotes: 1

Related Questions