pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
NUMERIC x VARCHAR
7+ messages / 5 participants
[nested] [flat]

* NUMERIC x VARCHAR
@ 2004-08-11 05:42  Er Galvão Abbott <galvao@galvao.eti.br>
  0 siblings, 2 replies; 7+ messages in thread

From: Er Galvão Abbott @ 2004-08-11 05:42 UTC (permalink / raw)
  To: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Verdana">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 field will never exceed 15 characters, and I will store 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&atilde;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>
</body>
</html>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 06:21  Scott Marlowe <smarlowe@qwest.net>
  parent: Er Galvão Abbott <galvao@galvao.eti.br>
  1 sibling, 1 reply; 7+ messages in thread

From: Scott Marlowe @ 2004-08-11 06:21 UTC (permalink / raw)
  To: galvao@galvao.eti.br; +Cc: pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 06:47  Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  parent: Er Galvão Abbott <galvao@galvao.eti.br>
  1 sibling, 1 reply; 7+ messages in thread

From: Pierre-Frédéric Caillaud @ 2004-08-11 06:47 UTC (permalink / raw)
  To: pgsql-performance


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,
>





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 07:27  Er Galvão Abbott <galvao@galvao.eti.br>
  parent: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Er Galvão Abbott @ 2004-08-11 07:27 UTC (permalink / raw)
  To: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-15"
 http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<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éric Caillaud wrote:<br>
<blockquote cite="midopscj2gwgscq72hf@musicbox" type="cite"><br>
Numeric won't store that :
  <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">&lt;galvao@galvao.eti.br&gt;</a> wrote:
  <br>
  <br>
  <blockquote type="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 type is 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>
</body>
</html>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 07:41  Michael Glaesemann <grzm@myrealbox.com>
  parent: Er Galvão Abbott <galvao@galvao.eti.br>
  0 siblings, 1 reply; 7+ messages in thread

From: Michael Glaesemann @ 2004-08-11 07:41 UTC (permalink / raw)
  To: galvao@galvao.eti.br; +Cc: pgsql-performance


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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 17:13  Er Galvão Abbott <galvao@galvao.eti.br>
  parent: Michael Glaesemann <grzm@myrealbox.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Er Galvão Abbott @ 2004-08-11 17:13 UTC (permalink / raw)
  To: pgsql-performance

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<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&atilde;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>
Michael Glaesemann wrote:<br>
<blockquote cite="midD909485D-EB69-11D8-831D-000A95C88220@myrealbox.com"
 type="cite"><br>
On Aug 11, 2004, at 4:27 PM, Er Galv&atilde;o Abbott wrote:
  <br>
  <br>
  <blockquote type="cite">It will. As I've said I wont be storing any
symbols.
    <br>
  </blockquote>
  <br>
It won't store leading zeros, however. This may or may not be an issue
for you.
  <br>
  <br>
  <br>
test=# create table tel (name_id integer not null, tel_numeric
numeric(15) not null, tel_varchar varchar(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>
&nbsp;name_id |&nbsp; tel_numeric&nbsp;&nbsp; |&nbsp;&nbsp; tel_varchar
  <br>
---------+----------------+-----------------
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 | 12345678911234 | 012345678911234
  <br>
(1 row)
  <br>
  <br>
I would do as another poster suggested: create a telephone number
domain as text with the check constraints you desire.
  <br>
  <br>
Michael Glaesemann
  <br>
grzm myrealbox com
  <br>
</blockquote>
</body>
</html>



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: NUMERIC x VARCHAR
@ 2004-08-11 19:19  Jan Wieck <JanWieck@Yahoo.com>
  parent: Scott Marlowe <smarlowe@qwest.net>
  0 siblings, 0 replies; 7+ messages in thread

From: Jan Wieck @ 2004-08-11 19:19 UTC (permalink / raw)
  To: Scott Marlowe <smarlowe@qwest.net>; +Cc: galvao@galvao.eti.br; pgsql-performance

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 #



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-08-11 19:19 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-11 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:21 ` Scott Marlowe <smarlowe@qwest.net>
2004-08-11 19:19   ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-11 06:47 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-11 07:27   ` Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 07:41     ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-11 17:13       ` Er Galvão Abbott <galvao@galvao.eti.br>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox