Size limit of character varying Postgresql

Question

What is the size limit of various data types in postgresql? I saw somewhere that for character varying(n), varchar(n) n must be between 1 to 10485760. Is that true?

What are the valid sizes for character(n), char(n) and text?

Answer 1

The maximum size of limited character types (e.g. varchar(n)) in Postgres is 10485760. You can check this in that way:

create table test(id serial primary key, str varchar(10485761));

ERROR:  length for type varchar cannot exceed 10485760

The limit is defined in the following fragment of source code (htup_details.h), however is not explicitly mentioned in the official documentation:

/*
 * MaxAttrSize is a somewhat arbitrary upper limit on the declared size of
 * data fields of char(n) and similar types.  It need not have anything
 * directly to do with the *actual* upper limit of varlena values, which
 * is currently 1Gb (see TOAST structures in postgres.h).  I've set it
 * at 10Mb which seems like a reasonable number --- tgl 8/6/00.
 */
#define MaxAttrSize     (10 * 1024 * 1024)

The maximum number of characters for variable unlimited length types (text, varchar) is undefined. There is a limit of size in bytes for all string types:

In any case, the longest possible character string that can be stored is about 1 GB.