PostgreSQL crosstabを使ったときに「ERROR: return and sql tuple descriptions are incompatible」が出るときの対処方法

現象

PostgreSQLでcrosstab関数を使ったときに

ERROR: return and sql tuple descriptions are incompatible

というエラーが出る。

環境

postgres=# select version();
                                                     version
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

実例

create extension tablefunc;

CREATE EXTENSION
postgres=# create table t(id text, detail text, count int);
CREATE TABLE
postgres=# insert into t(id, detail, count) values
postgres-#      ('test1','bad',1),
postgres-#      ('test1','good',2),
postgres-#      ('test1','ok',3),
postgres-#      ('test2','bad',4),
postgres-#      ('test2','good',5),
postgres-#      ('test2','ok',6);
INSERT 0 6
postgres=# select *
from crosstab(
    'select * from t'
)
as crossed(id text, bad bigint, good bigint, ok bigint);
ERROR:  return and sql tuple descriptions are incompatible

原因

crosstab関数にわたす引数のSQLが実際に返す行の型と、crossed(id text, bad bigint, good bigint, ok bigint)部分の型が一致していないことが原因でした。
上の例ではbigintと指定していたのが良くありませんでした。
自動で型変換してくれると思ったのですが、されないようですね。

解決方法

pg_typeof関数で実際の型を調べ、

postgres=# select pg_typeof(id), pg_typeof(count) from t;
 pg_typeof | pg_typeof
-----------+-----------
 text      | integer
 text      | integer
 text      | integer
 text      | integer
 text      | integer
 text      | integer
(6 rows)

他には\dで調べても良さそうです

postgres=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | text    |           |          |
 detail | text    |           |          |
 count  | integer |           |          |

integerだとわかったので、その型に合わせて指定することで解決しました。
(bigint -> intに変更しました)

postgres=# select *
postgres-# from crosstab(
postgres(#     'select * from t'
postgres(# )
postgres-# as crossed(id text, bad int, good int, ok int);
  id   | bad | good | ok
-------+-----+------+----
 test1 |   1 |    2 |  3
 test2 |   4 |    5 |  6
(2 rows)