Thread: ERROR: return and sql tuple descriptions are incompatible

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!

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