Postgresql – What determines whether `pg_dump` includes a varchar length

pg-dumppostgresql

I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns may be given without a length:

email character varying NOT NULL

or with a length:

email character varying(255) NOT NULL

Is there a configuration option that determines whether the length is included in the schema dump?

Best Answer

From the docco here, it says that

The notations varchar(n) and char(n) are aliases for character varying(n) and character(n), respectively. character without length specifier is equivalent to character(1). If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension.

Note the bit in bold. Your email fields with CHARACTER VARYING without a length are like TEXT fields. This is not good practice - you really only should set fields to this if you really don't know the longest possible value. From the correct answer to this thread, it's 254 characters. This area is treated well in this thread (also check out the link within the correct answer).

[EDIT]

Of course, as @DanielVérité points out, these are not the same fields from the same table - my answer was predicated on the first being the email in table_x and the second being the email in table_y. If they're meant to be the same field in the same table, someone has made a mistake, perhaps not huge, but there's a problem.

It's better to have CHARACTER VARYING(254) or VARCHAR(254) for all email fields. That way, if you attempt to put more than 254 characters into the email field, PostgreSQL will throw an error:

test=# create table x(y varchar(4));
CREATE TABLE
test=# insert into x values('sadfsadfa');
ERROR:  value too long for type character varying(4)
test=# select * from x;
 y 
---
(0 rows)

test=#
Related Question