pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: return and sql tuple descriptions are incompatible
13+ messages / 4 participants
[nested] [flat]
* ERROR: return and sql tuple descriptions are incompatible
@ 2024-03-05 17:17 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 13+ messages in thread
From: M Sarwar @ 2024-03-05 17:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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-i... <https://stackoverflow.com/questions/22052334/postgresql-says-return-and-sql-tuple-descriptions-are-i...; 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-03-05 18:06 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 13+ messages in thread
From: Erik Wienhold @ 2024-03-05 18:06 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-03-05 18:57 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 13+ messages in thread
From: M Sarwar @ 2024-03-05 18:57 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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...;
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: ERROR: return and sql tuple descriptions are incompatible
@ 2024-03-05 19:29 Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 13+ messages in thread
From: Wetmore, Matthew (CTR) @ 2024-03-05 19:29 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:ewie@ewie.name>>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto: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...;
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-03-05 19:35 M Sarwar <sarwarmd02@outlook.com>
parent: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
0 siblings, 0 replies; 13+ messages in thread
From: M Sarwar @ 2024-03-05 19:35 UTC (permalink / raw)
To: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>; Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:ewie@ewie.name>>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto: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...;
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-04 18:01 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 13+ messages in thread
From: M Sarwar @ 2024-05-04 18:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi all,
SELECT *
FROM CROSSTAB (
'
SELECT
PART.SERIAL_NUMBER,
TESTC.TEST_NUMBER,
TRESULT.TEST_RESULT
FROM bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART,
bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT,
bronx.TEST_TEST_DETAILS_ALL_MCM_INIT 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.STAGE = ''FT''
AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 ) = ''B7307631''
And TESTC.TEST_NUMBER = ''TEST1P1''
ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
'
) as ConcatenatedResults (
SERIALNUMBER character varying ,
TEST1P1 character
-- TEST_RESULT numeric
);
Error Encountered:
ERROR: return and sql tuple descriptions are incompatible SQL state: 42601
bronxdb1=> \d bronx.TEST_RESULTS_ALL_MCM_INIt
Table "bronx.test_results_all_mcm_init"
Column | Type | Collation | Nullable | Default
------------------------------+--------------------------+-----------+----------+----------------------------------
test_results_all_mcm_id | integer | | not null | generated by default as identity
test_part_details_all_mcm_id | bigint | | |
test_result | numeric | | |
test_test_details_all_mcm_id | integer | | |
bronxdb1=> \d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT
Table "bronx.test_test_details_all_mcm_init"
Column | Type | Collation | Nullable | Default
------------------------------+--------------------------+-----------+----------+----------------------------------
test_test_details_all_mcm_id | integer | | not null | generated by default as identity
stage | character(50) | | |
test_number | character(10) | | |
This is known issue and googled almost half day today and could not succeed.
There are lab works available on the internet but they are not helping in solving my issue.
Postgres Version 13.5
AWS RDS platform.
Thanks,
Sarwar
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-04 23:04 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 13+ messages in thread
From: Erik Wienhold @ 2024-05-04 23:04 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 2024-05-04 20:01 +0200, M Sarwar wrote:
> SELECT *
>
> FROM CROSSTAB (
>
> '
>
> SELECT
>
> PART.SERIAL_NUMBER,
>
> TESTC.TEST_NUMBER,
>
> TRESULT.TEST_RESULT
>
> FROM bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART,
>
> bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT,
>
> bronx.TEST_TEST_DETAILS_ALL_MCM_INIT 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.STAGE = ''FT''
>
> AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 ) = ''B7307631''
>
> And TESTC.TEST_NUMBER = ''TEST1P1''
>
> ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
>
> '
>
> ) as ConcatenatedResults (
>
> SERIALNUMBER character varying ,
>
> TEST1P1 character
>
> -- TEST_RESULT numeric
>
> );
>
>
>
> Error Encountered:
>
> ERROR: return and sql tuple descriptions are incompatible SQL state: 42601
The output value columns (TEST1P1 and TEST_RESULT) must be of the same
type. And it must be the same type as the third result column
(TRESULT.TEST_RESULT).
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-05 00:38 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 2 replies; 13+ messages in thread
From: M Sarwar @ 2024-05-05 00:38 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Erik,
TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER.
TRESULT.TEST_RESULT column is giving value data.
As I stated in my first email which has results of '\d bronx.TEST_RESULTS_ALL_MCM_INIt' and '\d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC.TEST_NUMBER and TRESULT.TEST_RESULT data types are numeric and character(10) . This is as per table definitions.
Are you suggesting me to use the type either TEXT or some other data type in the below clause of the SQL.
> ) as ConcatenatedResults (
> SERIALNUMBER character varying ,
> TEST1P1 character
> -- TEST_RESULT numeric
>
Thanks,
Sarwar
________________________________
From: Erik Wienhold <ewie@ewie.name>
Sent: Saturday, May 4, 2024 7:04 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-05-04 20:01 +0200, M Sarwar wrote:
> SELECT *
>
> FROM CROSSTAB (
>
> '
>
> SELECT
>
> PART.SERIAL_NUMBER,
>
> TESTC.TEST_NUMBER,
>
> TRESULT.TEST_RESULT
>
> FROM bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART,
>
> bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT,
>
> bronx.TEST_TEST_DETAILS_ALL_MCM_INIT 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.STAGE = ''FT''
>
> AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 ) = ''B7307631''
>
> And TESTC.TEST_NUMBER = ''TEST1P1''
>
> ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
>
> '
>
> ) as ConcatenatedResults (
>
> SERIALNUMBER character varying ,
>
> TEST1P1 character
>
> -- TEST_RESULT numeric
>
> );
>
>
>
> Error Encountered:
>
> ERROR: return and sql tuple descriptions are incompatible SQL state: 42601
The output value columns (TEST1P1 and TEST_RESULT) must be of the same
type. And it must be the same type as the third result column
(TRESULT.TEST_RESULT).
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-05 00:52 Scott Ribe <scott_ribe@elevated-dev.com>
parent: M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 13+ messages in thread
From: Scott Ribe @ 2024-05-05 00:52 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> On May 4, 2024, at 6:38 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Are you suggesting me to use the type either TEXT or some other data type in the below clause of the SQL.
No, but if you want them both in the same column of your output, then one or the other must be cast so the column type is consistent.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-05 00:56 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 13+ messages in thread
From: Erik Wienhold @ 2024-05-05 00:56 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 2024-05-05 02:38 +0200, M Sarwar wrote:
> TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER.
> TRESULT.TEST_RESULT column is giving value data.
Column TESTC.TEST_NUMBER (i.e. the second column) is ignored by crosstab
and it only requires the use of ORDER BY 1,2 to get correct results.
Output columns TEST1P1 and TEST_RESULT are two of the many value columns
which are only filled by the third result column.
I now remember it's the same issue from your very first crosstab mail:
https://www.postgresql.org/message-id/q2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx%40vz3cnoihz...
> As I stated in my first email which has results of
> '\d bronx.TEST_RESULTS_ALL_MCM_INIt' and
> '\d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC.TEST_NUMBER
> and TRESULT.TEST_RESULT data types are numeric and character(10) .
> This is as per table definitions.
>
> Are you suggesting me to use the type either TEXT or some other data
> type in the below clause of the SQL.
I'd say use numeric for TEST1P1 and TEST_RESULT. But the column names
and types suggest that you expect the second result column in the
crosstab output. Which isn't possible to my knowledge.
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-06 15:04 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 13+ messages in thread
From: M Sarwar @ 2024-05-06 15:04 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Erik,
I appreciate your response on this.
Now I have changed from ORDER BY 1,2 to ORDER BY 1.
Sorting TEST_NUMBER is taken care while generating TEST_NUMBER columns data.
Reference from the link.
AS concatenated (
SERIAL_NUMBER character varying(18),
TEST_RESULT_1 NUMERIC,
TEST_RESULT_2 NUMERIC
)
Question on this: As per this, there is no need to define the column TEST_NUMBER format. That means, database is identifying the category column ( TEST_NUMBER ) on it's own. Am I right?
Thank you,
Sarwar
________________________________
From: Erik Wienhold <ewie@ewie.name>
Sent: Saturday, May 4, 2024 8:56 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-05-05 02:38 +0200, M Sarwar wrote:
> TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER.
> TRESULT.TEST_RESULT column is giving value data.
Column TESTC.TEST_NUMBER (i.e. the second column) is ignored by crosstab
and it only requires the use of ORDER BY 1,2 to get correct results.
Output columns TEST1P1 and TEST_RESULT are two of the many value columns
which are only filled by the third result column.
I now remember it's the same issue from your very first crosstab mail:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fq...;
> As I stated in my first email which has results of
> '\d bronx.TEST_RESULTS_ALL_MCM_INIt' and
> '\d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC.TEST_NUMBER
> and TRESULT.TEST_RESULT data types are numeric and character(10) .
> This is as per table definitions.
>
> Are you suggesting me to use the type either TEXT or some other data
> type in the below clause of the SQL.
I'd say use numeric for TEST1P1 and TEST_RESULT. But the column names
and types suggest that you expect the second result column in the
crosstab output. Which isn't possible to my knowledge.
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-06 15:05 M Sarwar <sarwarmd02@outlook.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 13+ messages in thread
From: M Sarwar @ 2024-05-06 15:05 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Hi Scott,
Thanks for the clarification!
Sarwar
________________________________
From: Scott Ribe <scott_ribe@elevated-dev.com>
Sent: Saturday, May 4, 2024 8:52 PM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incompatible
> On May 4, 2024, at 6:38 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
>
> Are you suggesting me to use the type either TEXT or some other data type in the below clause of the SQL.
No, but if you want them both in the same column of your output, then one or the other must be cast so the column type is consistent.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: ERROR: return and sql tuple descriptions are incompatible
@ 2024-05-06 19:42 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 13+ messages in thread
From: Erik Wienhold @ 2024-05-06 19:42 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 2024-05-06 17:04 +0200, M Sarwar wrote:
> Now I have changed from ORDER BY 1,2 to ORDER BY 1.
This works because your input query only returns one category. But then
you don't need crosstab because you effectively only execute:
SELECT * FROM crosstab($$
SELECT PART.SERIAL_NUMBER, TESTC.TEST_NUMBER, TRESULT.TEST_RESULT
FROM ...
WHERE TESTC.TEST_NUMBER = 'TEST1P1'
ORDER BY 1
$$) AS ConcatenatedResults (
SERIALNUMBER varchar,
TEST_RESULT numeric
)
Which is the same as this plain query:
SELECT PART.SERIAL_NUMBER AS SERIALNUMBER, TRESULT.TEST_RESULT
FROM ...
WHERE TESTC.TEST_NUMBER = 'TEST1P1'
ORDER BY 1
Generally, you want something like ORDER BY 1,2 in the input query to
control how crosstab assigns the up to N different categories to N
value output columns *in a predictable manner*. With just ORDER BY 1
you rely on the unspecified order (at least when it comes to column 2)
in which the query returns rows.
> Sorting TEST_NUMBER is taken care while generating TEST_NUMBER columns data.
Do you mean the order of inserts into test_test_details_all_mcm_init?
You can't rely on that ordering in your queries unless you use ORDER BY.
The unspecified ordering depends (among other things) on the tuple order
on disk. Updating any of those rows affects that order because Postgres
updates rows by writing new tuples.
> Reference from the link.
>
> AS concatenated (
> SERIAL_NUMBER character varying(18),
> TEST_RESULT_1 NUMERIC,
> TEST_RESULT_2 NUMERIC
> )
>
>
> Question on this: As per this, there is no need to define the column
> TEST_NUMBER format. That means, database is identifying the category
> column ( TEST_NUMBER ) on it's own. Am I right?
With "TEST_NUMBER format" do you mean "output column"? If so, then yes,
you don't specify an output column for that. Values in columns 1 and 3
are the only ones you see in the crosstab output. Column 2 (with the
ORDER BY) only specifies whether test number 1 and 2 should be assigned
to TEST_RESULT_1 and TEST_RESULT_2 or the other way round.
This all is summarized in the docs[1]:
"In practice the SQL query should always specify ORDER BY 1,2 to ensure
that the input rows are properly ordered, that is, values with the same
row_name are brought together and correctly ordered within the row.
Notice that crosstab itself does not pay any attention to the second
column of the query result; it's just there to be ordered by, to control
the order in which the third-column values appear across the page."
[1] https://www.postgresql.org/docs/current/tablefunc.html#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT
--
Erik
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-05-06 19:42 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-05 17:17 ERROR: return and sql tuple descriptions are incompatible M Sarwar <sarwarmd02@outlook.com>
2024-03-05 18:06 ` Erik Wienhold <ewie@ewie.name>
2024-03-05 18:57 ` M Sarwar <sarwarmd02@outlook.com>
2024-03-05 19:29 ` Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
2024-03-05 19:35 ` M Sarwar <sarwarmd02@outlook.com>
2024-05-04 18:01 ERROR: return and sql tuple descriptions are incompatible M Sarwar <sarwarmd02@outlook.com>
2024-05-04 23:04 ` Re: ERROR: return and sql tuple descriptions are incompatible Erik Wienhold <ewie@ewie.name>
2024-05-05 00:38 ` Re: ERROR: return and sql tuple descriptions are incompatible M Sarwar <sarwarmd02@outlook.com>
2024-05-05 00:52 ` Re: ERROR: return and sql tuple descriptions are incompatible Scott Ribe <scott_ribe@elevated-dev.com>
2024-05-06 15:05 ` Re: ERROR: return and sql tuple descriptions are incompatible M Sarwar <sarwarmd02@outlook.com>
2024-05-05 00:56 ` Re: ERROR: return and sql tuple descriptions are incompatible Erik Wienhold <ewie@ewie.name>
2024-05-06 15:04 ` Re: ERROR: return and sql tuple descriptions are incompatible M Sarwar <sarwarmd02@outlook.com>
2024-05-06 19:42 ` Re: ERROR: return and sql tuple descriptions are incompatible Erik Wienhold <ewie@ewie.name>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox