Size limit of JSON data type in PostgreSQL

JsonPostgresqlPostgresql 9.2

Json Problem Overview


Does anyone know what is the limit on the size of JSON data type in PostgreSQL 9.2?

Json Solutions


Solution 1 - Json

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.

Solution 2 - Json

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionankurvsoniView Question on Stackoverflow
Solution 1 - Jsonj.w.rView Answer on Stackoverflow
Solution 2 - JsonMohammad BanisaeidView Answer on Stackoverflow