PostgreSQL 说“返回和 sql 元组描述不兼容”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22052334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL says "return and sql tuple descriptions are incompatible"
提问by user3357558
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).
请注意，HBW、HBO 和 NHB 列中的值是总计（总和）。
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.
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?
- 我在脚本中做错了什么？
- 我的脚本会产生所需的输出吗？
回答by Erwin Brandstetter
This works for me on Postgres 9.3:
这在 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. I can't provide an SQLfiddle, since one cannot install additional modules there.
产生所需的输出。我无法提供 SQLfiddle，因为无法在那里安装其他模块。
Essential changes
基本变化
- the table alias (bold
t) - the removed surplus column
"value" - the correct data type for your data columns (
double precisiona.k.a.float8)
- 表别名（粗体
t） - 移除的剩余列
"value" - 数据列的正确数据类型（
double precision又名float8）
The rest is a matter of taste and style. I wouldn't use valueas column name though, since it is a reserved word in SQL.
剩下的就是品味和风格的问题了。value不过，我不会用作列名，因为它是SQL 中的保留字。
Details in this related answer:
PostgreSQL Crosstab Query
此相关答案中的详细信息：
PostgreSQL Crosstab Query
