On 05/12/12 06:06, Edson Richter wrote:
Em 04/12/2012 14:59, hari.fuchs@xxxxxxxxx escreveu:
Edson Richter <edsonrichter@xxxxxxxxxxx> 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
/**/;/**/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
