Why does my Postgres crosstab produce the error: 'Query-specified return tuple has 7 columns but crosstab returns 6'?

Question

I'm using Postgres 8.4.9 and having trouble getting their crosstab to work.

Here are what my data look like:

   rowname     |       bucket        | bucket_value 
----------------+---------------------+--------------
 CL100001 10011 | 1 A01 | 01/01/2001  | 14 16
 CL100001 10011 | 1 A01 | 01/01/2001  |  
 CL100001 10011 | 1 A01 | 01/01/2001  |  
 CL100001 10011 | 1 A01 | 01/01/2001  | 440 480
 CL100001 10011 | 1 A01 | 01/01/2001  | 475 475
 CL100002 10021 | 1 B01 | 01/01/2001  | 16 16
 CL100002 10021 | 1 B01 | 01/01/2001  |  
 CL100002 10021 | 1 B01 | 01/01/2001  |  
 CL100002 10021 | 1 B01 | 01/01/2001  | 440 480
 CL100002 10021 | 1 B01 | 01/01/2001  | 475 475
 CL100003 10030 | 1 C01 | 01/01/2001  | 14 16
 CL100003 10030 | 1 C01 | 01/01/2001  |  
 CL100003 10030 | 1 C01 | 01/01/2001  |  
 CL100003 10030 | 1 C01 | 01/01/2001  | 440 440
 CL100003 10030 | 1 C01 | 01/01/2001  | 475 475

...

This is selected from a table, ct, that is used to prepare the data.

Next, my query looks like

SELECT gtreport.* FROM crosstab('SELECT 
    rowname, 
    bucket,
    bucket_value
FROM
    ct  
ORDER BY 
    rowname, bucket',
'SELECT DISTINCT
    markername  
FROM 
    markers M,
    genotypes G,
    gsamples S,
    guploads U
WHERE 
    M.markerid=G.markers_id
    AND G.gsamples_id=S.id 
    AND S.guploads_id=U.id 
    AND ( U.ibg_study_id=15 AND U.ibg_project_id is NULL) 
ORDER BY 
    M.markername')
AS gtreport(
 labid text, 
 box_well_run_date text, 
 HTTLPR text, 
 Amelo text,
 Caspi text,
 DAT1 text,
 DRD4 text)

Which I've tried to construct following the Postgres documentation as well as I can. But this produces the error "Query-specified return tuple has 7 columns but crosstab returns 6."

Which is baffling. If anyone sees what I've done wrong, it would be much appreciated....

--Rick

How many rows does your second query (`select distinct markername ...`) produce? — mu is too short, Dec 29 '11 at 01:24
need to clarify whats being returned in that second query for us...you give the make-up of ct but not markers. Are you expecting 2 distinct values and only 2 distinct values to come back from the second query? My preference on crosstabs is to create a select statement that joins the ID field to each of the 7 columns you want (creates 7 rows for each ID to populate the crosstab report) and then join this over to your other tables to populate the values as needed. — Twelfth, Dec 29 '11 at 19:38
Apologies; here is what the second query returns: markername ------------ 5HTTLPR Amelo Caspi DAT1 DRD4 (5 rows) — rixter, Dec 30 '11 at 19:25
Hello @Twelfth, Apologies; here is what the second query returns: markername ------------ 5HTTLPR Amelo Caspi DAT1 DRD4 (5 rows) These are the 5 column headers specified in the AS gtreport clause. Not sure I understand how to construct your suggested answer...but which sounds like what I need... thanks, rixter — rixter, Dec 30 '11 at 19:35
Hello @mu, Apologies; here is what the second query returns: markername ------------ 5HTTLPR Amelo Caspi DAT1 DRD4 (5 rows) These are the 5 column headers specified in the AS gtreport clause. Any suggestions appreciated... thanks, rixter — rixter, Dec 30 '11 at 19:36
Hey everybody: nevermind! I figured this out. So easy when you know how! 'Rowname' just had to include everything that repeats, 'bucket' had to contain the column headers of the data, and 'bucket_value' just had to contain the data. Quite simple, really, my dear Watson...(!) — rixter, Dec 30 '11 at 21:46
could you add this to the answers so I can upvote it and remove this from the list of unanswered questions? — Chris Travers, Mar 24 '13 at 09:48

score 0 · Answer 1 · answered Mar 27 '13 at 14:44

Your issue is box_well_run_date is not found in your result set.

Try this instead:

SELECT gtreport.* FROM crosstab('SELECT 
    rowname, 
    bucket,
    bucket_value
FROM
    ct  
ORDER BY 
    rowname, bucket',
'SELECT DISTINCT
    markername  
FROM 
    markers M,
    genotypes G,
    gsamples S,
    guploads U
WHERE 
    M.markerid=G.markers_id
    AND G.gsamples_id=S.id 
    AND S.guploads_id=U.id 
    AND ( U.ibg_study_id=15 AND U.ibg_project_id is NULL) 
ORDER BY 
    M.markername')
AS gtreport(
 labid text, 
 HTTLPR text, 
 Amelo text,
 Caspi text,
 DAT1 text,
 DRD4 text);

Why does my Postgres crosstab produce the error: 'Query-specified return tuple has 7 columns but crosstab returns 6'?

1 Answers1