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?

58246 次浏览

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 14.