Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get:

ERROR: tables can have at most 1600 columns

I want to know if it is possible to raise the maximum number of columns. Is there a configuration variable I can change? Do I have to compile postgres with special options? Is there a good reason why the maximum number of columns is 1600 instead of 1700?

I have searched for this answer on Google and all answers are essentially: "you should never have a table with 1600 columns".

share|improve this question
4  
And what is your answer to "Why do you need 1600 columns in the first place?" –  ypercube Apr 16 '13 at 15:51
    
Those Google hits and @ypercube are right. Anyway, you possibly can modifiy the PostgreSQL source so that it allows more columns, but don't do that. –  dezso Apr 16 '13 at 16:04
1  
As part of the problem definition, I am given a table with 1600+ columns. That cannot be changed. I have a software library that could be modified to condense or ignore columns, but I'm wondering if there is a quick fix. This is one of those situations which you never expected that you would encounter. –  m0nty Apr 16 '13 at 16:11
    
PostgreSQL is open source, so yes, it is possible to change the maximum number of columns a table can have. –  Colin 't Hart Apr 16 '13 at 17:09
1  
@Colin'tHart, you are technically correct, but I'm not going to do that. I solved the problem at the application level by ignoring enough unneeded columns to get below 1600. –  m0nty Apr 17 '13 at 18:01

2 Answers 2

up vote 1 down vote accepted

There's no run-time configuration option that will let you have more than about 1600 columns in a table. You can redefine values and compile PostgreSQL from source. That might turn out to be harder than it sounds in your case.

A quick look at the source code seems to say the number of columns depends on the values of MaxTupleAttributeNumber and MaxHeapAttributeNumber. These are defined in src/include/access/htup_details.h.

So you might be able to increase the number of columns by

  • redefining those values, while
  • paying close attention to how their current values have been determined, then
  • recompiling.

Details are in source code comments. Search for htup_details.h in the search dialog there.

I suggest you try building from source without making any changes first. After you can do that well, then try making the changes above. Consider digging around in the source code to determine whether there are any other dependencies that are not as well documented as these two.

share|improve this answer
    
Thanks. I solved the problem at the application level by ignoring enough columns to get below 1600. You have the answer to my question though: "There's no run-time config..." –  m0nty Apr 17 '13 at 18:15

How much control do you have over the queries used by the library?

You could use multiple tables to store the data and serve it up using views to provide specific column-sets if the consumer is able to make more intelligent requests then "Select * from honking_big_table". Views have the same column limits as tables unfortunately.

It really comes down to how this massive data set is being used.

Personally, I would wrap the access with stored procedures to shield the application from whatever black magic was needed to get this to work.

share|improve this answer
    
Welcome on DBA.SE! Could you elaborate your last sentence a bit? :) –  dezso Apr 16 '13 at 20:08
    
A view on postgre cannot contain more then ~1600 columns (I ran a simple test to confirm). –  marceljg Apr 17 '13 at 13:16
    
Interesting ideas... I solved the problem at the application level by ignoring enough unneeded columns to get below 1600. –  m0nty Apr 17 '13 at 17:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.