PostgreSQL says "return and sql tuple descriptions are incompatible"

Question

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
3   NHB    1409818.328
3   HBW    220430.7922
3   HBO    323512.9391
4   NHB    48711.3814
4   HBW    163385.1575
4   HBO    363352.3441

That I want to reorganize as:

ID     HBO             HBW              NHB
1   700905.7243    164216.1311      700905.7243
2   82152.072      89543.2972       146023.3792
3   323512.9391    220430.7922      1409818.328
4   363352.3441    163385.1575      48711.3814

Please note that the values in columns HBW, HBO and NHB are totals (sum).

Here is the script I am using to create the output:

-- CREATE EXTENSION tablefunc;

SELECT *
FROM  CROSSTAB
(
    'SELECT _tlfd.id,   
    _tlfd."class",
    _tlfd."value"
    FROM public._tlfd
    WHERE _tlfd."class" = ''HBW'' or _tlfd."class" = ''HBO'' or _tlfd."class" = ''NHB'' 
    ORDER BY 1,2'
) 
    AS
(
    "class" int, 
    "HBW" text,
    "HBO" text,
    "NHB" text,
    --"Purpose" varchar, 
    "value" double precision
);

When I run the script I get this error:

ERROR:  return and sql tuple descriptions are incompatible.

I am not sure what this means and how to correct the error. Can someone please let me know:

What am I doing wrong in the script?
Will my script produce the desired output?

Try removing `"Weight" double precision` from crosstab row definition. — Ihor Romanchenko, Feb 26 '14 at 20:26
It looks like your "HBW", "HBO", ... columns should be `NUMERIC`, not `TEXT`. — Daniel Vérité, Feb 26 '14 at 20:47
Igor and Daniel, I tried your solutions but the error remains !!!!. I have also edited the script to reflect proper field names. — user3357558, Feb 26 '14 at 21:30
It would be best to provide the table definition with data types - what you get with `\d tbl` in `psql`. Assuming `double precision` for your data columns. Adapt to your actual data type ... — Erwin Brandstetter, Feb 26 '14 at 23:22

Erwin Brandstetter · Accepted Answer · 2020-11-20T05:09:44.390

18

This works for me on Postgres 9.3:

SELECT *
FROM   crosstab (
 $$SELECT id, class, "value"
   FROM   _tlfd
   WHERE  class = ANY ('{HBW, HBO, NHB}')
   ORDER  BY 1,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

edited Nov 20 '20 at 05:09

answered Feb 26 '14 at 23:01

Erwin Brandstetter

605,456
145
1,078
1,228

This worked like a charm. Thank you. THe $$ before SELECT was something I could not find in the documentation. – user3357558 Feb 26 '14 at 23:49
1

@user3357558: Dollar-quoting is explained (plus link to documentation) in the link at the end of my answer. – Erwin Brandstetter Feb 26 '14 at 23:55

PostgreSQL says "return and sql tuple descriptions are incompatible"

1 Answers1

Essential changes

Linked