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

On 05/12/12 06:06, Edson Richter wrote:
> Em 04/12/2012 14:59, hari.fuchs@gmail.com escreveu:
>> Edson Richter <edsonrichter@hotmail.com> writes:
>>
>>> In this specific case, the full length (14) is mandatory... so seems
>>> there is no loss or gain.
>>> Also, I see all varchar(...) created are by default "storage =
>>> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric,
>>> smallint, integer) are "storage = MAIN".
>>> 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"?
>> Sounds like premature optimization to me.  I'd first express what I want
>> as clear as possible, e.g. "CREATE DOMAIN BrazilianCompanyId AS
>> char(14)",
>> and try to spot and fix performance problems when I'm done with all
>> that.
>>
>>
>>
> Actually, I already stressed performance over these fields (query
> optimization, indexing, reverse indexing, full text index inside
> PostgreSQL and outside PostgreSQL, etc).
> At current stage, I'm just looking for finetuning. Maybe storage is
> one possibility.
> It's already established database that I can't make big changes (even
> changing from varchar to decimal or bigint would not be possible
> because of leading zeroes).
>
> Thanks for all that provided hints! I've learned a lot with you all.
>
> Regards,
>
> Edson
>
>
If your number is always the same length, you don't need to store the
zeros in the database, so you can use bigint! You can add the leading
zeros when you display to the user.

More specifically, you could add leading zeros in the SQL you use to
extract the value from the database.
N.B. lpad(*) truncates values larger than the field size!

For example:

DROP TABLE IF EXISTS tabzer;

CREATE TABLE tabzer
(
     id      SERIAL PRIMARY KEY,
     payload bigint
);

INSERT INTO tabzer (payload)
VALUES
     (123),
     (1234567890),
     (1234567890123456),
     (12345678901234567) ;

TABLE tabzer;

SELECT
     lpad(t.payload::text, 16, '0')
FROM
     tabzer t
/**/;/**/