From: Erik Wienhold <ewie@ewie.name>
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
Date: Mon, 6 May 2024 21:42:02 +0200
Message-ID: <3e96cfd1-e6fb-4a25-97c1-bd2c74284f78@ewie.name> (raw)
In-Reply-To: <DM4PR19MB5978F92AECE1ECB4548706D2D31C2@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <PH2P110MB0922C5109BE107AD82331420FF1EA@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<DM4PR19MB59783F61D28BE07B43F68C31D31E2@DM4PR19MB5978.namprd19.prod.outlook.com>
<vulsb3n6dcd7wzjuhslppt3oij3nkwqh2olagmx2ibtnjzpccu@7c2jvcyjm2x2>
<DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2@DM4PR19MB5978.namprd19.prod.outlook.com>
<7kadkskdcixknub2dvc4ovm24q5thdptxj2opu42pninbadonm@h5opztcvzcrh>
<DM4PR19MB5978F92AECE1ECB4548706D2D31C2@DM4PR19MB5978.namprd19.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ewie@ewie.name, sarwarmd02@outlook.com, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: return and sql tuple descriptions are incompatible
In-Reply-To: <3e96cfd1-e6fb-4a25-97c1-bd2c74284f78@ewie.name>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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