The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?
For current PostgreSQL version (up to 9.5), queries are received by a backend in a
Stringinfo buffer, which is limited to
MaxAllocSize, defined as:
#define MaxAllocSize ((Size) 0x3fffffff) /* 1 gigabyte - 1 */
So a query is limited to 1 gigabyte (2^30) in size, minus 1 byte for a terminating null byte.
Should a client try to send a larger query, an error looking like this would come back:
ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 0 bytes by N more bytes.
N is the size of the query.
Be aware that a query just below
1GB might require large amounts of memory to be parsed, planned or executed, in addition to that
If you need to push a large series of literals into a query, consider the alternative of creating a temporary table,
COPY rows into it and have the main query refer to that temporary table.