Edson Richter wrote:
> Also, I see all varchar(...) created are by default "storage =
> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric,
> smallint, integer) are "storage = MAIN".
That's unlikely to matter on a 14 character value.
> Can I have a gain using fixed length datatype in place of
> current varchar (like "numeric (14,0)")?
> Or changing to "char(14) check length(doc)=14" and
> "storage=MAIN"?
In PostgreSQL char(n) is never, ever, under any circumstances
faster than varchar(n) to store or retrieve. char(n) is stored
exactly the same as varchar(n) except that before storing the
length is checked and spaces are added if necessary to fill it out
to the maximum length, and when comparing spaces are stripped
before using the value in comparisons to other strings. The
semantics of char(n) are confusing and very odd. Personally, I
recommend never, ever using char(n).
PostgreSQL provides a function to check the storage length in bytes
for various types of objects (although some of them might be
compressed or stored out of line under some circumstances).
test=# select pg_column_size('12345678901234'::char(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('1'::char(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('12345678901234'::varchar(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('1'::varchar(14));
pg_column_size
----------------
5
(1 row)
test=# select pg_column_size('12345678901234'::numeric(14,0));
pg_column_size
----------------
14
(1 row)
test=# select pg_column_size('1'::numeric(14,0));
pg_column_size
----------------
8
(1 row)
test=# select pg_column_size('12345678901234'::bigint);
pg_column_size
----------------
8
(1 row)
If your value is always 14 numeric digits, bigint would save space
and generally be faster than varcher(14).
-Kevin
