Thread: ERROR: return and sql tuple descriptions are incompatible

ERROR: return and sql tuple descriptions are incompatible

From
M Sarwar
Date:
Hello All,

 

I am trying to run the below SQL.
SELECT * FROM CROSSTAB (
'SELECT
PART.SERIAL_NUMBER ,
TESTC.TEST_NUMBER,
TRESULT.TEST_RESULT
FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
AND PART.DATE1 = ''12/01/2023''
and PART.STAGE = ''BI''
AND TESTC.TEST_NUMBER = ''TEST1P2''
ORDER BY 1, 2'
)
AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
;
I am getting the below error message.

ERROR: return and sql tuple descriptions are incompatible
SQL state: 42601

I followed the link, https://stackoverflow.com/questions/22052334/postgresql-says-return-and-sql-tuple-descriptions-are-incompatible and that is not helping!



I have tried to change the data type SERIAL_NUMBER , TEST_NUMBER and TEST_RESULT and that did not help.
Appreciate any help in this regard.

Thanks,
Sarwar

Re: ERROR: return and sql tuple descriptions are incompatible

From
Erik Wienhold
Date:
On 2024-03-05 18:17 +0100, M Sarwar wrote:
> I am trying to run the below SQL.
> SELECT * FROM CROSSTAB (
> 'SELECT
> PART.SERIAL_NUMBER ,
> TESTC.TEST_NUMBER,
> TRESULT.TEST_RESULT
> FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
> BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
> BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
> WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> AND PART.DATE1 = ''12/01/2023''
> and PART.STAGE = ''BI''
> AND TESTC.TEST_NUMBER = ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
> ;
> I am getting the below error message.
> 
> ERROR: return and sql tuple descriptions are incompatible
> SQL state: 42601

The problem is that you define TEST_NUMBER as an output column.  You
must instead define the output row as one group column (SERIAL_NUMBER)
and N value columns (TEST_RESULT) all of the same type.  For example
with 2 value columns:

    AS concatenated (
        SERIAL_NUMBER character varying(18),
        TEST_RESULT_1 NUMERIC,
        TEST_RESULT_2 NUMERIC
    )

The second column (TEST_NUMBER) of your query is ignored by crosstab()
and only used to ensure that values in the same group are properly layed
out in the output row.

That is also explained with examples in the docs:
https://www.postgresql.org/docs/current/tablefunc.html#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT

-- 
Erik



Re: ERROR: return and sql tuple descriptions are incompatible

From
M Sarwar
Date:
Hello Erik,
Second column TEST_NUMBER is type CHARACTER (10) which non numeric data.
Thanks,
Sarwar


From: Erik Wienhold <ewie@ewie.name>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incompatible
 
On 2024-03-05 18:17 +0100, M Sarwar wrote:
> I am trying to run the below SQL.
> SELECT * FROM CROSSTAB (
> 'SELECT
> PART.SERIAL_NUMBER ,
> TESTC.TEST_NUMBER,
> TRESULT.TEST_RESULT
> FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
> BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
> BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
> WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> AND PART.DATE1 = ''12/01/2023''
> and PART.STAGE = ''BI''
> AND TESTC.TEST_NUMBER = ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
> ;
> I am getting the below error message.
>
> ERROR: return and sql tuple descriptions are incompatible
> SQL state: 42601

The problem is that you define TEST_NUMBER as an output column.  You
must instead define the output row as one group column (SERIAL_NUMBER)
and N value columns (TEST_RESULT) all of the same type.  For example
with 2 value columns:

        AS concatenated (
            SERIAL_NUMBER character varying(18),
            TEST_RESULT_1 NUMERIC,
            TEST_RESULT_2 NUMERIC
        )

The second column (TEST_NUMBER) of your query is ignored by crosstab()
and only used to ensure that values in the same group are properly layed
out in the output row.

That is also explained with examples in the docs:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Ftablefunc.html%23TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT&data=05%7C02%7C%7C8826a46f3d7d47b7aa6508dc3d3f0f61%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638452588247259565%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=V2cICNmt8zEsOAJO%2BZE3OEDblPOj9aFk2RJkii76QdI%3D&reserved=0

--
Erik

RE: ERROR: return and sql tuple descriptions are incompatible

From
"Wetmore, Matthew (CTR)"
Date:

Character is also deprecated, and will pad space up to 10

 

From: M Sarwar <sarwarmd02@outlook.com>
Sent: Tuesday, March 5, 2024 10:57 AM
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: ERROR: return and sql tuple descriptions are incompatible

 

Hello Erik,

Second column TEST_NUMBER is type CHARACTER (10) which non numeric data.

Thanks,

Sarwar

 


From: Erik Wienhold <ewie@ewie.name>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incompatible

 

On 2024-03-05 18:17 +0100, M Sarwar wrote:
> I am trying to run the below SQL.
> SELECT * FROM CROSSTAB (
> 'SELECT
> PART.SERIAL_NUMBER ,
> TESTC.TEST_NUMBER,
> TRESULT.TEST_RESULT
> FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
> BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
> BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
> WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> AND PART.DATE1 = ''12/01/2023''
> and PART.STAGE = ''BI''
> AND TESTC.TEST_NUMBER = ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
> ;
> I am getting the below error message.
>
> ERROR: return and sql tuple descriptions are incompatible
> SQL state: 42601

The problem is that you define TEST_NUMBER as an output column.  You
must instead define the output row as one group column (SERIAL_NUMBER)
and N value columns (TEST_RESULT) all of the same type.  For example
with 2 value columns:

        AS concatenated (
            SERIAL_NUMBER character varying(18),
            TEST_RESULT_1 NUMERIC,
            TEST_RESULT_2 NUMERIC
        )

The second column (TEST_NUMBER) of your query is ignored by crosstab()
and only used to ensure that values in the same group are properly layed
out in the output row.

That is also explained with examples in the docs:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Ftablefunc.html%23TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT&data=05%7C02%7C%7C8826a46f3d7d47b7aa6508dc3d3f0f61%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638452588247259565%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=V2cICNmt8zEsOAJO%2BZE3OEDblPOj9aFk2RJkii76QdI%3D&reserved=0

--
Erik

Re: ERROR: return and sql tuple descriptions are incompatible

From
M Sarwar
Date:
Matt,
Thanks for updating. I will plan to convert to varying char(10).
Sarwar

From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
Sent: Tuesday, March 5, 2024 2:29 PM
To: M Sarwar <sarwarmd02@outlook.com>; Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: ERROR: return and sql tuple descriptions are incompatible
 

Character is also deprecated, and will pad space up to 10

 

From: M Sarwar <sarwarmd02@outlook.com>
Sent: Tuesday, March 5, 2024 10:57 AM
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: ERROR: return and sql tuple descriptions are incompatible

 

Hello Erik,

Second column TEST_NUMBER is type CHARACTER (10) which non numeric data.

Thanks,

Sarwar

 


From: Erik Wienhold <ewie@ewie.name>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incompatible

 

On 2024-03-05 18:17 +0100, M Sarwar wrote:
> I am trying to run the below SQL.
> SELECT * FROM CROSSTAB (
> 'SELECT
> PART.SERIAL_NUMBER ,
> TESTC.TEST_NUMBER,
> TRESULT.TEST_RESULT
> FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
> BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
> BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
> WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> AND PART.DATE1 = ''12/01/2023''
> and PART.STAGE = ''BI''
> AND TESTC.TEST_NUMBER = ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
> ;
> I am getting the below error message.
>
> ERROR: return and sql tuple descriptions are incompatible
> SQL state: 42601

The problem is that you define TEST_NUMBER as an output column.  You
must instead define the output row as one group column (SERIAL_NUMBER)
and N value columns (TEST_RESULT) all of the same type.  For example
with 2 value columns:

        AS concatenated (
            SERIAL_NUMBER character varying(18),
            TEST_RESULT_1 NUMERIC,
            TEST_RESULT_2 NUMERIC
        )

The second column (TEST_NUMBER) of your query is ignored by crosstab()
and only used to ensure that values in the same group are properly layed
out in the output row.

That is also explained with examples in the docs:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Ftablefunc.html%23TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT&data=05%7C02%7C%7C8826a46f3d7d47b7aa6508dc3d3f0f61%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638452588247259565%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=V2cICNmt8zEsOAJO%2BZE3OEDblPOj9aFk2RJkii76QdI%3D&reserved=0

--
Erik