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

  • 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

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.

  • 1
    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.

  • 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
  1. Consider re-design so you don't need more then 1600 columns in a table.

If that doesn't work:

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

Or if that wouldn't accomodate the need:

  1. 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));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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