Postgresql Says "return And Sql Tuple Descriptions Are Incompatible"

I have the following data: ID CLASS VALUE 1 NHB 700905.7243 1 HBW 164216.1311 1 HBO 700905.7243 2 NHB 146023.3792 2 HBW 89543.2972 2 HBO 82152.072

Solution 1:

This works for me on Postgres 9.3:

SELECT *
FROM   crosstab (
 $$SELECT id, class, "value"FROM   _tlfd
   WHEREclass = ANY ('{HBW, HBO, NHB}')ORDERBY1,2$$
   ) AS t (
        class int,                   -- needs a table alias!
        "HBW" float8,                -- resulting columns are double precision!
        "HBO" float8,
        "NHB" float8
        -- "value"double precision  -- column does not exist in result!
    );

Produces the desired output.

Essential changes

• the table alias (bold t)
• the removed surplus column "value"
• the correct data type for your data columns (double precision a.k.a. float8)

The rest is a matter of taste and style. I wouldn't use value as column name though, since it is a reserved word in SQL.

Basics for crosstab() queries here:

• PostgreSQL Crosstab Query