In Postgresql, is it possible to change the maximum number of columns a table can have?

Question

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".

Answer 1

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.

Answer 2

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.

Answer 3

1. Consider re-design so you don't need more then 1600 columns in a table.

If that doesn't work:

2. Consider using JSON/JSONB field for the excess columns.

Or if that wouldn't accomodate the need:

3. Consider a spreadsheet like table, as follows:

   create table spreadsheet_table (
    colnum integer not null
   ,colname varchar(30) not null
   ,coltype varchar(30) not null
   ,colnullable boolean not null
   ,collen integer
   ,colprecission integer
   ,colscale integer
   ,CONSTRAINT pk_spreadsheet PRIMARY KEY (colnum));
   
   create table spreadsheet_cell (
    rownum integer not null
   ,colnum integer not null CONSTRAINT fk_spreadsheet_cell REFERENCES spreadsheet_table (colnum)
   ,value bytea
   ,CONSTRAINT pk_spreadsheet_cell PRIMARY KEY (rownum, colnum));