Re: ERROR: return and sql tuple descriptions are incompatible
| От | M Sarwar |
|---|---|
| Тема | Re: ERROR: return and sql tuple descriptions are incompatible |
| Дата | |
| Msg-id | DM4PR19MB5978F92AECE1ECB4548706D2D31C2@DM4PR19MB5978.namprd19.prod.outlook.com обсуждение исходный текст |
| Ответ на | Re: ERROR: return and sql tuple descriptions are incompatible (Erik Wienhold <ewie@ewie.name>) |
| Ответы |
Re: ERROR: return and sql tuple descriptions are incompatible
|
| Список | pgsql-admin |
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
)
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
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%2Fq2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx%2540vz3cnoihz4j5&data=05%7C02%7C%7C8da071bf7da546cf704308dc6c9e3155%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638504673913926130%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=P4PCSXea%2BCFbHMdkL679n03L5SVYUd4Kp5VGgEhu7aw%3D&reserved=0
> 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
> 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%2Fq2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx%2540vz3cnoihz4j5&data=05%7C02%7C%7C8da071bf7da546cf704308dc6c9e3155%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638504673913926130%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=P4PCSXea%2BCFbHMdkL679n03L5SVYUd4Kp5VGgEhu7aw%3D&reserved=0
> 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
В списке pgsql-admin по дате отправления: