Re: Which is faster: char(14) or varchar(14)

Em 04/12/2012 12:53, Kevin Grittner escreveu:
> 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).

Thanks, I've learned a lot.
Now, I'll make my home work.

Regards,

Edson

>
> -Kevin
>
>