PostgreSQL 说“return 和 sql 元组描述不兼容"； [英] PostgreSQL says "return and sql tuple descriptions are incompatible"

问题描述

我有以下数据:

ID 类别值1 NHB 700905.72431 HBW 164216.13111 HBO 700905.72432 NHB 146023.37922 HBW 89543.29722 HBO 82152.0723 NHB 1409818.3283 HBW 220430.79223 HBO 323512.93914 NHB 48711.38144 HBW 163385.15754 HBO 363352.3441

我想重组为:

ID HBO HBW NHB1 700905.7243 164216.1311 700905.72432 82152.072 89543.2972 146023.37923 323512.9391 220430.7922 1409818.3284 363352.3441 163385.1575 48711.3814

请注意，HBW、HBO 和 NHB 列中的值是总计(总和).

这是我用来创建输出的脚本:

-- CREATE EXTENSION tablefunc;选择 *来自 CROSSTAB('选择_tlfd.id，_tlfd."类",_tlfd."值"FROM public._tlfdWHERE _tlfd."class" = ''HBW'' 或 _tlfd."class" = ''HBO'' 或 _tlfd."class" = ''NHB''按 1,2' 订购)作为(类" int，HBW"文本，HBO"文本，NHB"文本，--目的" varchar,值"双精度);

当我运行脚本时出现此错误:

<块引用>

错误:return 和 sql 元组描述不兼容.

我不确定这意味着什么以及如何更正错误.谁能告诉我:

1. 我在脚本中做错了什么?
2. 我的脚本会产生所需的输出吗?

解决方案

这适用于 Postgres 9.3:

选择 *从交叉表($$SELECT id，类，值"来自 _tlfdWHERE 类 = ANY ('{HBW, HBO, NHB}')按 1,2$$ 订购) 作为 t (class int, -- 需要一个表别名！"HBW" float8, -- 结果列是双精度的！HBO"浮动8，NHB"浮动8-- "value" 双精度 -- 结果中不存在列！);

产生所需的输出.

基本变化

• 表格别名(粗体t)
• 删除的剩余列value"
• 数据列的正确数据类型(双精度又名float8)

剩下的就是品味和风格的问题了.我不会使用 value 作为列名，因为它是一个 SQL 中的保留字.

crosstab() 查询的基础知识在这里:

• PostgreSQL 交叉表查询

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:

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

解决方案

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

这篇关于PostgreSQL 说“return 和 sql 元组描述不兼容"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！