Vryjdfkk

SQLite: How can we configure the maximum number of columns allowed in a table?

You would have to obtain the Source Code for SQLLite, change the value of SQLITE_MAX_COLUMN, recompile the code and distrbute the resulting executable.

However, more than 2000 columns in a single table sounds wrong to me.
Do you have lots of repeated fields in each row? That's poor Relational design.

If necessary, create a second table with the same primary key as the first and add your additional fields into that. You would have to issue two select statements to retrieve the values, though, because SQLITE_MAX_COLUMNS also applies to select statements, so you can't just join the two tables together.

As per SQLite documentation Limits In SQLite

"Limits" in the context of sizes or quantities that can not be exceeded. We are concerned with things like the maximum number of bytes in a BLOB or the maximum number of columns in a table.

[...]

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.

As specified in Limits In SQLite:

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.

You will have to compile SQLite with the corresponding compile-time option SQLITE_MAX_COLUMN as described in How To Compile SQLite

I downloaded and unpacked the snapshot version from the Download page,

• First ran ./configure and


• Waited till the Makefile had been created


• Opened the Makefile and added -DSQLITE_MAX_COLUMN=32767 to the end of the line starting DEFS = and saved the file.


• Finally, ran make to compile the binary (this is for Linux/macOS/*nix, though)

For an edge case I also had to increase the following options (none of this is recommended)

-DSQLITE_MAX_COLUMN=32767 -DSQLITE_MAX_SQL_LENGTH=1073741824 -DSQLITE_MAX_LENGTH=1273741824 -DSQLITE_MAX_FUNCTION_ARG=127 -DSQLITE_MAX_VARIABLE_NUMBER=65534