Thread: NUMERIC x VARCHAR
<font face="Verdana">Greetings.<br /><br /> I have a question regarding performance of certain datatypes:<br /><br /> I havea field where I will store my clients phone numbers. I know that this field will never exceed 15 characters, and I willstore only numbers here (no dashes, dots, etc...), so I was wondering:<br /><br /> Wich type is faster: NUMERIC(15,0)or VARCHAR(15)? Are there any storage differences between them?<br /><br /> TIA,<br /></font> <pre class="moz-signature"cols="72">-- Er Galvão Abbott Desenvolvedor Web <a class="moz-txt-link-freetext" href="http://www.galvao.eti.br/">http://www.galvao.eti.br/</a> <a class="moz-txt-link-abbreviated" href="mailto:galvao@galvao.eti.br">galvao@galvao.eti.br</a></pre>
On Tue, 2004-08-10 at 23:42, Er Galvão Abbott wrote: > Greetings. > > I have a question regarding performance of certain datatypes: > > I have a field where I will store my clients phone numbers. I know > that this field will never exceed 15 characters, and I will store only > numbers here (no dashes, dots, etc...), so I was wondering: > > Wich type is faster: NUMERIC(15,0) or VARCHAR(15)? Are there any > storage differences between them? Since numerics are stored as text strings, the storage would be similar. Numerics, however, may be slower since they have more constraints built in. If you throw a check constraint on the varchar(15) then it will likely be about the same speed for updating. text type with a check contraint it what i'd use. That way if you want to change it at a later date you just drop and recreate your constraint.
Numeric won't store that :
(+33) 4 01 23 45 67
On Wed, 11 Aug 2004 02:42:33 -0300, Er Galvão Abbott
<galvao@galvao.eti.br> wrote:
> Greetings.
>
> I have a question regarding performance of certain datatypes:
>
> I have a field where I will store my clients phone numbers. I know that
> this
> field will never exceed 15 characters, and I will store only numbers
> here (no
> dashes, dots, etc...), so I was wondering:
>
> Wich type is faster: NUMERIC(15,0) or VARCHAR(15)? Are there any storage
> differences between them?
>
> TIA,
>
<font face="Verdana">It will. As I've said I wont be storing any symbols.<br /><br /></font> <pre class="moz-signature" cols="72">-- Er Galvão Abbott Desenvolvedor Web <a class="moz-txt-link-freetext" href="http://www.galvao.eti.br/">http://www.galvao.eti.br/</a> <a class="moz-txt-link-abbreviated" href="mailto:galvao@galvao.eti.br">galvao@galvao.eti.br</a></pre><br /><br /> Pierre-FrédéricCaillaud wrote:<br /><blockquote cite="midopscj2gwgscq72hf@musicbox" type="cite"><br /> Numeric won't storethat : <br /><br /> (+33) 4 01 23 45 67 <br /><br /> On Wed, 11 Aug 2004 02:42:33 -0300, Er Galvão Abbott <a class="moz-txt-link-rfc2396E"href="mailto:galvao@galvao.eti.br"><galvao@galvao.eti.br></a> wrote: <br /><br /><blockquotetype="cite">Greetings. <br /><br /> I have a question regarding performance of certain datatypes: <br /><br/> I have a field where I will store my clients phone numbers. I know that this <br /> field will never exceed 15 characters,and I will store only numbers here (no <br /> dashes, dots, etc...), so I was wondering: <br /><br /> Wich typeis faster: NUMERIC(15,0) or VARCHAR(15)? Are there any storage <br /> differences between them? <br /><br /> TIA, <br/><br /></blockquote><br /><br /><br /> ---------------------------(end of broadcast)--------------------------- <br />TIP 6: Have you searched our list archives? <br /><br /> <a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a><br/><br /><br /></blockquote><br /><pre class="moz-signature"cols="72"> </pre>
On Aug 11, 2004, at 4:27 PM, Er Galvão Abbott wrote:
> It will. As I've said I wont be storing any symbols.
It won't store leading zeros, however. This may or may not be an issue
for you.
test=# create table tel (name_id integer not null, tel_numeric
numeric(15) not null, tel_varchar varchar(15) not null);
CREATE TABLE
test=# insert into tel (name_id, tel_numeric, tel_varchar) values
(1,012345678911234, '012345678911234');
INSERT 17153 1
test=# select * from tel;
name_id | tel_numeric | tel_varchar
---------+----------------+-----------------
1 | 12345678911234 | 012345678911234
(1 row)
I would do as another poster suggested: create a telephone number
domain as text with the check constraints you desire.
Michael Glaesemann
grzm myrealbox com
<font face="Verdana">Thanks, Michael.<br /><br /> You and "Evil Azrael" (lol) got me. Never thought about leading zeros.<br/><br /> Varchar it is!<br /><br /> Thanks a lot,</font><br /><pre class="moz-signature" cols="72">-- Er Galvão Abbott Desenvolvedor Web <a class="moz-txt-link-freetext" href="http://www.galvao.eti.br/">http://www.galvao.eti.br/</a> <a class="moz-txt-link-abbreviated" href="mailto:galvao@galvao.eti.br">galvao@galvao.eti.br</a></pre><br /><br /> MichaelGlaesemann wrote:<br /><blockquote cite="midD909485D-EB69-11D8-831D-000A95C88220@myrealbox.com" type="cite"><br />On Aug 11, 2004, at 4:27 PM, Er Galvão Abbott wrote: <br /><br /><blockquote type="cite">It will. As I've said I wont bestoring any symbols. <br /></blockquote><br /> It won't store leading zeros, however. This may or may not be an issue foryou. <br /><br /><br /> test=# create table tel (name_id integer not null, tel_numeric numeric(15) not null, tel_varcharvarchar(15) not null); <br /> CREATE TABLE <br /> test=# insert into tel (name_id, tel_numeric, tel_varchar) values(1,012345678911234, '012345678911234'); <br /> INSERT 17153 1 <br /> test=# select * from tel; <br /> name_id | tel_numeric | tel_varchar <br /> ---------+----------------+----------------- <br /> 1 | 12345678911234 | 012345678911234<br /> (1 row) <br /><br /> I would do as another poster suggested: create a telephone number domain as textwith the check constraints you desire. <br /><br /> Michael Glaesemann <br /> grzm myrealbox com <br /></blockquote>
On 8/11/2004 2:21 AM, Scott Marlowe wrote: > On Tue, 2004-08-10 at 23:42, Er Galvão Abbott wrote: >> Greetings. >> >> I have a question regarding performance of certain datatypes: >> >> I have a field where I will store my clients phone numbers. I know >> that this field will never exceed 15 characters, and I will store only >> numbers here (no dashes, dots, etc...), so I was wondering: >> >> Wich type is faster: NUMERIC(15,0) or VARCHAR(15)? Are there any >> storage differences between them? > > Since numerics are stored as text strings, the storage would be > similar. Numerics, however, may be slower since they have more > constraints built in. If you throw a check constraint on the > varchar(15) then it will likely be about the same speed for updating. They are stored as an array of signed small integers holding digits in base-10000, plus a precision, scale and sign. That's somewhat different from text strings, isn't it? Jan > > text type with a check contraint it what i'd use. That way if you want > to change it at a later date you just drop and recreate your constraint. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #