Maximum number of columns in a table, which one is correct?

user_1165201 0
Viewed 2729

What is the maximum number of columns in an SQLite3 table? I found some answer :

Maximum Number Of Columns The maximum number of columns is 32767 in a table. The default setting for SQLITE_MAX_COLUMN is 2000.

But I see the error of exceeding maximum number of columns when column number is 1000. So which one is correct? I use SQLAlchemy 1.3.8 with Python.

sqlalchemy sqlite python
3 Answers

I think you pretty much answered your question your self.

As per the official documentation here here

Maximum Number Of Columns

The SQLITE_MAX_COLUMN compile-time parameter is used to set an upper bound on:

  • The number of columns in a table The number of columns in an index

  • The number of columns in a view The number of terms in the SET clause of an UPDATE statement The number of columns in the result set of a SELECT statement The number of terms in a GROUP BY or ORDER BY clause

  • The number of values in an INSERT statement

The default setting for SQLITE_MAX_COLUMN is 2000. You can change it at compile time to values as large as 32767.

On the other hand, many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

Other use full answer to this you can find it here: How to change SQLITE_MAX_COLUMN in Python?

And here: How to increase column limit of a table in SQLite?

user_139039420
user_45553260

There is no general answer to your question. As you already found, the maximum configurable number of colums is 32767. However, the key is that it is configurable, so it can be adjusted. Compare https://www.sqlite.org/limits.html

The maximum number of columns can be lowered at run-time using the sqlite3_limit(db,SQLITE_LIMIT_COLUMN,size) interface.

Seems that in your case, the limit has been set to 1000, as you apparently run into that limit. However, the page above goes on to say that

[...] many experienced database designers will argue that a well-normalized database will never need more than 100 columns in a table.

So you may either want to increase the SQLITE_MAX_COLUMN setting or maybe rethink your db design.

user_140157370
Related