By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE marginality_by_channel (dist_chann_name int, year int, operative_margin int);
INSERT INTO marginality_by_channel VALUES
('1', '2020', '20783'),
('1', '2021', '5791'),
('2', '2020', '30362'),
('3', '2021', '14501'),
('3', '2020', '2765'),
('3', '2021', '4535');
6 rows affected
SELECT *
FROM crosstab(
$$
SELECT dist_chann_name, year, operative_margin
FROM marginality_by_channel
ORDER BY 1, 2
$$
, 'VALUES (2020), (2021)'
) AS ct ("DC" int, "2020" int, "2021" int);
| DC | 2020 | 2021 |
|---|---|---|
| 1 | 20783 | 5791 |
| 2 | 30362 | null |
| 3 | 2765 | 4535 |
-- This works, too, with the 2-parameter form:
SELECT *
FROM crosstab(
$$
SELECT dist_chann_name::text, year, operative_margin::text
FROM marginality_by_channel
ORDER BY 1, 2
$$
, 'VALUES (2020), (2021)'
) AS ct ("DC" int, "2020" int, "2021" int);
| DC | 2020 | 2021 |
|---|---|---|
| 1 | 20783 | 5791 |
| 2 | 30362 | null |
| 3 | 2765 | 4535 |
-- But not with the 1-parameter form:
SELECT *
FROM crosstab(
$$
SELECT dist_chann_name::text, year, operative_margin
FROM marginality_by_channel
ORDER BY 1, 2
$$
) AS ct ("DC" int, "2020" int, "2021" int);
ERROR: invalid return type
DETAIL: SQL rowid datatype does not match return rowid datatype.
-- And this raises your error exactly:
SELECT *
FROM crosstab(
$$
SELECT dist_chann_name, year, operative_margin::text -- !
FROM marginality_by_channel
ORDER BY 1, 2
$$
) AS ct ("DC" int, "2020" int, "2021" int);
ERROR: return and sql tuple descriptions are incompatible