Query returning different number of rows, results in crosstab error

Question

I get this error with my query, but I can't figure out what is wrong with it:

Query-specified return tuple has 135 columns but crosstab returns 295.

And here's my query: (The part highlighted in bold returns same number of rows when run separately in pgAdmin.)

SELECT X.*, pi.productcode, pi.productitemdesc, pi.retailsalesprice, cat.productcategorydesc FROM ( 
SELECT * FROM crosstab ( 
'SELECT a.productitem AS productitemid, l.locationcode, (CASE WHEN SUM(a.netamount) IS NOT NULL THEN SUM(a.netamount) ELSE 0 END) || ''#'' || (CASE WHEN SUM(a.quantity) IS NOT NULL THEN SUM(a.quantity) ELSE 0 END) AS sales_qty FROM invoiceitem a INNER JOIN invoiceinfo b ON a.invoice = b.invoiceid INNER JOIN locationinfo l ON b.location = l.locationid WHERE b.status !=2 AND l.locationtype = 1 AND l.status = 1 AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')>=''2018-03-01'' AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')<=''2018-03-03'' GROUP BY a.productitem, l.locationcode ORDER BY a.productitem', 
'SELECT l.locationcode FROM locationinfo l INNER JOIN invoiceinfo b ON b.location = l.locationid
 WHERE b.status !=2 AND l.locationtype = 1 AND l.status = 1 AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')>=''2018-03-01'' AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')<=''2018-03-03'' GROUP BY l.locationcode order by l.locationcode') 
 AS (productitemid int, "0007" text,"BE101" text,"BE1013" text,"BE1014" text,"BE102" text,"BE103" text,"BE1034" text,"BE104" text,"BE1040" text,"BE1043" text,"BE1044" text,"BE1045" text,"BE1046" text,"BE105" text,"BE106" text,"BE107" text,"BE108" text,"BE109" text,"BE110" text,"BE111" text,"BE112" text,"BE123" text,"BE1265" text,"BE1266" text,"BE1271" text,"BE1272" text,"BE1273" text,"BE1274" text,"BE1279" text,"BE1280" text,"BE1281" text,"BE1282" text,"BE1351" text,"BE1400" text,"BE1401" text,"BE1404" text,"BE141" text,"BE142" text,"BE193" text,"BE194" text,"BE2125" text,"BE2126" text,"BE2127" text,"BE2128" text,"BE3001" text,"BE3002" text,"BE3005" text,"BE3006" text,"BE3009" text,"BE3010" text,"BE3031" text,"BE3032" text,"BE3121" text,"BE3122" text,"BE3123" text,"BE3124" text,"BE3127" text,"BE3128" text,"BE3131" text,"BE3132" text,"BE3203" text,"BE3204" text,"BE325" text,"BE3253" text,"BE3254" text,"BE326" text,"BE332" text,"BE3503" text,"BE3504" text,"BE355" text,"BE356" text,"BE365" text,"BE366" text,"BE381" text,"BE382" text,"BE383" text,"BE384" text,"BE400" text,"BE401" text,"BE402" text,"BE403" text,"BE405" text,"BE406" text,"BE408" text,"BE409" text,"BE411" text,"BE412" text,"BE4311" text,"BE4316" text,"BE4401" text,"BE4402" text,"BE4521" text,"BE4522" text,"BE4551" text,"BE4552" text,"BE470" text,"BE473" text,"BE475" text,"BE481" text,"BE482" text,"BE601" text,"BE604" text,"BE609" text,"BE610" text,"BE7040" text,"BE7043" text,"BE7045" text,"BE7046" text,"BE7048" text,"BE7049" text,"BE708" text,"BE7111" text,"BE7112" text,"BE7127" text,"BE7128" text,"BE7217" text,"BE7218" text,"BE7307" text,"BE7308" text,"BE7351" text,"BE7352" text,"BE801" text,"BE802" text,"BE803" text,"BE804" text,"BE831" text,"BE832" text,"BE860" text,"BE861" text,"BE862" text,"BE863" text,"BE865" text,"BE981" text,"BE982" text 
 )) X
 LEFT JOIN productitem pi ON X.productitemid = pi.productitemid
 LEFT JOIN productcategory cat ON pi.productcategory = cat.productcategoryid

The bold part of

productitemid int, "0007" text,"BE101" text,"BE1013" text,"BE1014" text,"BE102" text,"BE103" text,"BE1034" text,"BE104" text,"BE1040" text,"BE1043" text,"BE1044" text,"BE1045" text,"BE1046" text,"BE105" text,"BE106" text,"BE107" text,"BE108" text,"BE109" text,"BE110" text,"BE111" text,"BE112" text,"BE123" text,"BE1265" text,"BE1266" text,"BE1271" text,"BE1272" text,"BE1273" text,"BE1274" text,"BE1279" text,"BE1280" text,"BE1281" text,"BE1282" text,"BE1351" text,"BE1400" text,"BE1401" text,"BE1404" text,"BE141" text,"BE142" text,"BE193" text,"BE194" text,"BE2125" text,"BE2126" text,"BE2127" text,"BE2128" text,"BE3001" text,"BE3002" text,"BE3005" text,"BE3006" text,"BE3009" text,"BE3010" text,"BE3031" text,"BE3032" text,"BE3121" text,"BE3122" text,"BE3123" text,"BE3124" text,"BE3127" text,"BE3128" text,"BE3131" text,"BE3132" text,"BE3203" text,"BE3204" text,"BE325" text,"BE3253" text,"BE3254" text,"BE326" text,"BE332" text,"BE3503" text,"BE3504" text,"BE355" text,"BE356" text,"BE365" text,"BE366" text,"BE381" text,"BE382" text,"BE383" text,"BE384" text,"BE400" text,"BE401" text,"BE402" text,"BE403" text,"BE405" text,"BE406" text,"BE408" text,"BE409" text,"BE411" text,"BE412" text,"BE4311" text,"BE4316" text,"BE4401" text,"BE4402" text,"BE4521" text,"BE4522" text,"BE4551" text,"BE4552" text,"BE470" text,"BE473" text,"BE475" text,"BE481" text,"BE482" text,"BE601" text,"BE604" text,"BE609" text,"BE610" text,"BE7040" text,"BE7043" text,"BE7045" text,"BE7046" text,"BE7048" text,"BE7049" text,"BE708" text,"BE7111" text,"BE7112" text,"BE7127" text,"BE7128" text,"BE7217" text,"BE7218" text,"BE7307" text,"BE7308" text,"BE7351" text,"BE7352" text,"BE801" text,"BE802" text,"BE803" text,"BE804" text,"BE831" text,"BE832" text,"BE860" text,"BE861" text,"BE862" text,"BE863" text,"BE865" text,"BE981" text,"BE982" text

AND

SELECT l.locationcode FROM locationinfo l INNER JOIN invoiceinfo b ON b.location = l.locationid
 WHERE b.status !=2 AND l.locationtype = 1 AND l.status = 1 AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')>=''2018-03-01'' AND TO_CHAR (b.invoicedate, ''YYYY-MM-DD'')&lt;=''2018-03-03'' GROUP BY l.locationcode order by l.locationcode

When run seperately, I get 295 results which is correct. However putting it together in the whole query gets the error.

if crosstab returns 295 columns and if you return it all , then number of column should be 295 in return. If you just need 135 columns crosstab should only retunr 135 — parlad, Apr 07 '18 at 03:20
"same number of rows" ... same as which number? Am I right in assuming your column `invoiceinfo.invoicedate` is type `timestamptz`? — Erwin Brandstetter, Apr 07 '18 at 03:56
@ErwinBrandstetter thanks for comment, I have put in the details in my updated question. TQ ! — Sylph, Apr 07 '18 at 04:05
@parladneupane thanks for comment. If i separate out the two queries, I get same number which is 295, however when I put into the whole query I get the error. I have updated my question. Hope that explains more. Thank you very much — Sylph, Apr 07 '18 at 04:05

score 2 · Answer 1 · answered Apr 07 '18 at 04:28

The manual:

The remaining output columns must have the type of the last column of the source_sql query's result, and there must be exactly as many of them as there are rows in the category_sql query's result.

crosstab(text, text) with a dynamic (!) SELECT query as 2nd parameter is very error prone, since the output column list is static. You should also generate the output column list dynamically. Example:

Dynamic alternative to pivot with CASE and GROUP BY

That aside, the different number of rows returned from the same query, may very well be due to a different timezone setting in the two different sessions.

You have the expression TO_CHAR(b.invoicedate, 'YYYY-MM-DD') in your predicates. If b.invoicedate is type timestamptz the result depends on the timezone setting. Consider:

SET timezone = '+10';
SELECT TO_CHAR(timestamptz '2018-04-07 23:30+0', 'YYYY-MM-DD');

to_char
----------
2018-04-08

SET timezone = '-10';
SELECT TO_CHAR(timestamptz '2018-04-07 23:30+0', 'YYYY-MM-DD');

to_char
----------
2018-04-07

Solution

To remove the dependency on the timezone setting, use absolute values.

to_char() is expensive complication in this place to begin with. Drop that and adjust the predicate.
Provide timestamptz values to match your column. This also allows indexes to be used (if applicable).
- Return Table Type from A function in PostgreSQL
Use time zone names (not offsets or abbreviations) to avoid more misunderstandings.
- Ignoring timezones altogether in Rails and PostgreSQL
Use dollar-quoting to clean up the quote mess.
- Insert text with single quotes in PostgreSQL

I suggest this as 2nd parameter:

$$
SELECT l.locationcode
FROM   locationinfo l
JOIN   invoiceinfo  b ON b.location = l.locationid
WHERE  b.status <> 2 AND l.locationtype = 1 AND l.status = 1
AND    b.invoicedate >=  timestamptz '2018-03-01 Europe/Vienna'
AND    b.invoicedate <   timestamptz '2018-03-04 Europe/Vienna'
GROUP  BY 1
ORDER  BY 1
$$

Replace Europe/Vienna in my example with the time zone name defining your dates.
Note 2018-03-04 to include all of 2018-03-03 like you intended.

Thanks a lot for your help. I just checked and the invoice data type is : timestamp without timezone. If that is the case, do you have any idea what could be wrong? I tried to use your method too , but still have the same error. Btw, thanks a lot for your help above, learning something new everyday. — Sylph, Apr 07 '18 at 04:45

Query returning different number of rows, results in crosstab error

1 Answers1

Solution