String field length in Postgres SQL

Question

I have a string filed in an SQL database, representing a url. Some url's are short, and some very long. I don't really know waht's the longest URL I might encounter, so to be on the safe side, I'll take a large value, such as 256 or 512.

When I define the maximal string length (using SQLAlchemy for example):

url_field = Column(String(256))

Does this take up space (storage) for each row, even if the actual string is shorter?

I'm assuming this has to do with the implementation details. I'm using postgreSQL, but am interested in sqlite, mysql also.

Answer 1

In PostgreSQL character(n) is basically just varchar with space padding on input/output. It's clumsy and should be avoided. It consumes the same storage as a varchar or text field that's been padded out to the maximum length (see below). char(n) is a historical wart, and should be avoided - at least in PostgreSQL it offers no advantages and has some weird quirks with things like left(...).

varchar(n), varchar and text all consume the same storage - the length of the string you supplied with no padding. It only uses the storage actually required for the characters, irrespective of the length limit. Also, if the string is null, PostgreSQL doesn't store a value for it at all (not even a length header), it just sets the null bit in the record's null bitmap.

Qualified varchar(n) is basically the same as unqualified varchar with a check constraint on length(colname) < n.

Despite what some other comments/answers are saying, char(n), varchar, varchar(n) and text are all TOASTable types. They can all be stored out of line and/or compressed. To control storage use ALTER TABLE ... ALTER COLUMN ... SET STORAGE.

If you don't know the max length you'll need, just use text or unqualified varchar. There's no space penalty.

For more detail see the documentation on character data types, and for some of the innards on how they're stored, see database physical storage in particular TOAST.

Demo:

CREATE TABLE somechars(c10 char(10), vc10 varchar(10), vc varchar, t text);
insert into somechars(c10) values ('  abcdef ');
insert into somechars(vc10) values ('  abcdef ');
insert into somechars(vc) values ('  abcdef ');
insert into somechars(t) values ('  abcdef ');

Output of this query for each col:

SELECT 'c10', pg_column_size(c10), octet_length(c10), length(c10) 
from somechars where c10 is not null;

is:

 ?column? | pg_column_size | octet_length | length 
 c10      |             11 |           10 |      8
 vc10     |             10 |            9 |      9
 vc       |             10 |            9 |      9
 t        |             10 |            9 |      9

pg_column_size is the on-disk size of the datum in the field. octet_length is the uncompressed size without headers. length is the "logical" string length.

So as you can see, the char field is padded. It wastes space and it also gives what should be a very surprising result for length given that the input was 9 chars, not 8. That's because Pg can't tell the difference between leading spaces you put in yourself, and leading spaces it added as padding.

So, don't use char(n).

BTW, if I'm designing a database I never use varchar(n) or char(n). I just use the text type and add appropriate check constraints if there are application requirements for the values. I think that varchar(n) is a bit of a wart in the standard, though I guess it's useful for DBs that have on-disk layouts where the size limit might affect storage.

Answer 2

Both postgreSQL, sqllite and mysql applies the sql standard for storing varchar and chars. Which is basiclly this:

SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n characters in length. An attempt to store a longer string into a column of these types will result in an error, unless the excess characters are all spaces, in which case the string will be truncated to the maximum length. (This somewhat bizarre exception is required by the SQL standard.) If the string to be stored is shorter than the declared length, values of type character will be space-padded; values of type character varying will simply store the shorter string.

If one explicitly casts a value to character varying(n) or character(n), then an over-length value will be truncated to n characters without raising an error. (This too is required by the SQL standard.)

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.

Reference:

• Pg docs: Character Types

Answer 3

Usually database storage engines can do many thing you don't expect. But basically, there are two kinds of text fields, that give a hint what will go on internally.

char and varchar. Char will give you a fixed field column and depending on the options in the sql session, you may receive space filled strings or not. Varchar is for text fields up to a certain maximum length.

Varchar fields can be stored as a pointer outside the block, so that the block keeps a predictable size on queries - but that is an implementation detail and may vary from db to db.