Thread: NUMERIC x VARCHAR

NUMERIC x VARCHAR

From
Er Galvão Abbott
Date:
<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>

Re: NUMERIC x VARCHAR

From
"Scott Marlowe"
Date:
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.


Re: NUMERIC x VARCHAR

From
Pierre-Frédéric Caillaud
Date:
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,
>



Re: NUMERIC x VARCHAR

From
Er Galvão Abbott
Date:
<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>

Re: NUMERIC x VARCHAR

From
Michael Glaesemann
Date:
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


Re: NUMERIC x VARCHAR

From
Er Galvão Abbott
Date:
<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>

Re: NUMERIC x VARCHAR

From
Jan Wieck
Date:
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 #