Unexpected Effect Of Filtering On Result From Crosstab() Query

I have a crosstab() query like the following: SELECT * FROM crosstab( 'SELECT row_name, extra1, extra2..., another_table.category, value FROM table t JOIN another_table ON

Solution 1:

extra1, extra2, ... are "extra columns" in crosstab terminology. The manual for the tablefunc module explains the rules:

It may also have one or more “extra” columns. The row_name column must be first. The category and value columns must be the last two columns, in that order. Any columns between row_name and category are treated as “extra”. The “extra” columns are expected to be the same for all rows with the same row_name value.

And further down:

The output row_name column, plus any “extra” columns, are copied from the first row of the group.

Bold emphasis on key parts by me.

You only sort by row_name:

ORDERBY row_name ASC

Does not matter in the first example where you filter with:

WHERE ... t.extra1 ='val1'-- single quotes by me

All input row have extra1 = 'val1' anyway. But it matters in the second example where you filter with:

WHERE ... t.extra1 IN('val1', ...) --> More values

Now, the first bolded requirement above is violated for the extra column extra1. While the sort order of the first input query is non-deterministic, resulting values for the "extra" column extra1 are picked arbitrarily. The more possible values for extra1, the fewer rows will end up having 'val1': that's what you observed.

You can still make it work: to report extra1 = 'val1' for every row_name that has at least one of those, change the ORDER BY to:

ORDERBY row_name, (extra1 <> 'val1')

Sorts 'val1' on top. Explanation for that boolean expression (with links to more):

• PostgreSQL: Grouping then filtering table, with condition for nonexistence

Other "extra" columns are still chosen arbitrarily while the sort order is not deterministic.

Crosstab basics:

• PostgreSQL Crosstab Query
• Postgresql crosstab query with multiple "row name" columns