PostgreSQL error: cannot use more than 32 columns in an index

Question

Using SQLAlchemy and Python with PostgreSQL as the database. I have a table with more than 32 columns and I am getting the following error:

cannot use more than 32 columns in an index

Here's a code snippet of how I am creating the tables:

class Application():
    __tablename__ = 'application'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    value = Column(Integer)
    # ..and many other columns (45 columns in total)

   _columns = ['name', 'value'...] # a list of 45 column names

   __table_args__ = (UniqueConstraint(*_columns),)

Seems like there is a build option for PostgreSQL to increase the number of columns to more than 32: http://www.postgresql.org/docs/8.3/static/indexes-multicolumn.html From the above link:

This limit can be altered when building PostgreSQL; see the file pg_config_manual.h

But wondering if there is a way to limit the index to fewer than 32 columns when creating a table through SQLAlchemy APIs. I really don't like the having to rebuild the PostgreSQL database for this.

How are you defining your tables? (paste some code!) I don't think it'll run off and create massive indexes like that all on its own. — Jay Kominek
– Jay Kominek, Commented Jun 5, 2015 at 18:54
I think its possibly the UniqueConstraint class that is using all the columns as indexes! — kaush
– kaush, Commented Jun 5, 2015 at 19:48

Jay Kominek · Accepted Answer · 2015-06-05 19:56:20Z

1

Yes, as you speculate, the issue is that you've set up a unique constraint containing every column in the table. That constraint is in part implemented by making an index on the table referring to all of those columns. Are you sure that that's what you want?

If you've got a table that's some sort of name value mapping, as yours appears to be, including the values in the uniqueness constraint doesn't make any sense. Right now you could have one row of (1,'compiler','/usr/bin/gcc') and another row of (2,'compiler','/usr/bin/clang'), which might (in some uses) be odd.

If instead you want to ensure every value in every column is unique (which seems excessive, but probably makes more sense), you should create the roughly ~45 UniqueConstraints, one for each column.

answered Jun 5, 2015 at 19:56

Jay Kominek

8,7831 gold badge37 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kaush Over a year ago

Yes, I just verified it was indeed UniqueConstraint that was making every column as index. I also do not want every column to be unique but rather a few. So I think I should be all set. Thanks for your help.

Collectives™ on Stack Overflow

PostgreSQL error: cannot use more than 32 columns in an index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related