PostgreSQL Simple Crosstable (Rows to Columns and Columns to Rows) in a big data set

Question

I have a table looking like this:

CELL   day1      day2      day3      day4    ......   day365
1      3,7167    0         0         0,1487  ......   0,3256
2      0         0         0,2331    0,1461  ......   1,8765
3      1,431     0,4121    0         1,4321  ......   0
...
...
...
64800

I would like to transponse my table, so that I have my rows as columns and my columns as rows. A result looking like this:

DAY    1         2         3       ...... 64800  
day1   3,7167    0         1,431   ...... ......
day2   0         0         0,4121  ...... ......
day3   0         0,2331    0       ...... ......
day4   0,1487    0,1461    1,4321  ...... ......
...
...
...
day365

My biggest problem is the table size (365 columns and 64800 rows). How do I write a query where I dont have to define the columns I want in the output. Is there a way to create a table without defining each column and could you show my how my query would have to look like?

My second problem is that I dont even get the crosstab to work in a datasubset . The following query:

SELECT * FROM crosstab(
'SELECT * FROM 1997_subset  ORDER BY 1,2')
AS test("cell" int, "day1" double precision, "day2" double precision, "day3" double precision, "day4" double precision, "day5" double precision)

Gives me this mistake:

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.

Help is very much appreciated, thank you very much!

You are probably looking for this SO answer ( http://stackoverflow.com/questions/3002499/postgresql-crosstab-query ) — Robins Tharakan, Oct 12 '15 at 08:43
Number of columns is limited to 250-1600, see [About PostgreSQL](http://www.postgresql.org/about/). — klin, Oct 12 '15 at 08:49
Why would want 64800 columns? What do these columns represent? — CodeMonkey, Oct 12 '15 at 08:51
The columns represent the world in a grid. Each column represents an area of the world, each one is one degree long and one degree high. Makes 64800 in total. — marius, Oct 12 '15 at 08:54
@marius thats fair but why not create a table like (Long, Lat, Day, Value) it will be much easier to work with in the long run. What would happen to your table if you want to store values for multiple years? Would you then create a new table or create multiples of 365 columns for each year? — CodeMonkey, Oct 12 '15 at 10:14
@CodeMonkey that is exactly what I am going to do now, and you giving me the same idea confirms my theory that this is the best way to go now, so thank you very much. I am going to create a table with geometry, day and precipitation (value), might coming up with a couple of more questions ;) Thanks everyone for helping me! — marius, Oct 12 '15 at 11:02
@marius I think that is a good idea. I'm not a postgresql guy but reading the documentation for `crosstab` it looks like it will make it easier to work with if keeping it in a unpivoted normalized structure. — CodeMonkey, Oct 12 '15 at 11:07

Craig Ringer · Accepted Answer · 2015-10-12T11:16:04.023

You can't do this within PostgreSQL, because it's limited to around 1600 columns in output. Otherwise the crosstab function from the tablefunc module would help you.

You'll need to extract the data to CSV using COPY then use an external tool that can pivot the data. Take a look at ETL tools, or at worst, you might have to script it.

I just checked and Talend at least can do a pivot (row/column transpose, crosstab). Pentaho, CloverETL, etc probably can too.

PostgreSQL Simple Crosstable (Rows to Columns and Columns to Rows) in a big data set

1 Answers1