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.
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.