Size limit of JSON data type in PostgreSQL
Does anyone know what is the limit on the size of JSON data type in PostgreSQL 9.2?
Looking at the source for PostgreSQL 9.2.1:
Source: postgresql-9.2.1\src\backend\utils\adt\json.c:
/*
* Input.
*/
Datum
json_in(PG_FUNCTION_ARGS)
{
char *text = PG_GETARG_CSTRING(0);
json_validate_cstring(text);
/* Internal representation is the same as text, for now */
PG_RETURN_TEXT_P(cstring_to_text(text));
}
Update for PostgreSQL 9.3.5:
The code has changed in the json_in
function, but the json internal representation is still text:
Source: postgresql-9.3.5\src\backend\utils\adt\json.c:
/*
* Input.
*/
Datum
json_in(PG_FUNCTION_ARGS)
{
char *json = PG_GETARG_CSTRING(0);
text *result = cstring_to_text(json);
JsonLexContext *lex;
/* validate it */
lex = makeJsonLexContext(result, false);
pg_parse_json(lex, &nullSemAction);
/* Internal representation is the same as text, for now */
PG_RETURN_TEXT_P(result);
}
So it appears that, for now at least, json
is the same as a text
datatype but with JSON validation. The text
datatype's maximum size is 1GB.
For jsonb
fields, if you look at the jsonb.c in the source code, you'll see this function:
checkStringLen(size_t len)
{
if (len > JENTRY_OFFLENMASK)
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("string too long to represent as jsonb string"),
errdetail("Due to an implementation restriction, jsonb strings cannot exceed %d bytes.",
JENTRY_OFFLENMASK)));
return len;
}
Error code says that jsonb strings cannot exceed JENTRY_OFFLENMASK
bytes.
In jsonb.h, that constant is defined as:
#define JENTRY_OFFLENMASK 0x0FFFFFFF
which is 255 MB.
I checked this in the source code for PostgreSQL 9.4 up to 13.