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?
2I would just say that if you're even approaching any sort of limit of that nature, you would need to reconsider your design. I have found that, usually, any queries that are coming close are normally generated by some sort of framework (involving extreme string concatenation) and they tend to lose contact with reality.– VéraceMar 6, 2016 at 9:14
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.
@Quandary: I don't see what change you're referring to. stringinfo.h currently says "StringInfo provides an extensible string data type (currently limited to a length of 1GB)" Jun 16, 2020 at 15:12
I was refering to stackoverflow.com/questions/4936731/… - but i see this post ist from 2011 - if the 1GB is now, then it appears to have been shortened, not extended. My fault for not looking at the date. PS: MaxAllocSize is now defined in github.com/postgres/postgres/blob/master/src/common/… and there it notes: /* It's possible we could use a different value for this in frontend code */– QuandaryJun 17, 2020 at 6:39
@Quandary: interesting, I wouldn't have thought it ever was 2GB and I'm still skeptical. I'll do some research and update the answer. Jun 17, 2020 at 8:34
I'm not sure about that, might just be bogus info from SO in the first place.– QuandaryJun 17, 2020 at 10:25