Re: ERROR: return and sql tuple descriptions are incompatible

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]


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($$
        FROM ...
        ORDER BY 1
    $$) AS ConcatenatedResults (
        SERIALNUMBER varchar,
        TEST_RESULT numeric

Which is the same as this plain query:

    FROM ...
    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),
> )
> 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."



[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux