pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNUMERIC x VARCHAR
7+ messages / 5 participants
[nested] [flat]
* NUMERIC x VARCHAR
@ 2004-08-11 05:42 Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:21 ` Re: NUMERIC x VARCHAR Scott Marlowe <smarlowe@qwest.net>
2004-08-11 06:47 ` Re: NUMERIC x VARCHAR Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
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ã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 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 ` Re: NUMERIC x VARCHAR Jan Wieck <JanWieck@Yahoo.com>
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 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:21 ` Re: NUMERIC x VARCHAR Scott Marlowe <smarlowe@qwest.net>
@ 2004-08-11 19:19 ` Jan Wieck <JanWieck@Yahoo.com>
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
* Re: NUMERIC x VARCHAR
2004-08-11 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
@ 2004-08-11 06:47 ` Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-11 07:27 ` Re: NUMERIC x VARCHAR 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 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:47 ` Re: NUMERIC x VARCHAR 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 ` Re: NUMERIC x VARCHAR Michael Glaesemann <grzm@myrealbox.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"><galvao@galvao.eti.br></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 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:47 ` Re: NUMERIC x VARCHAR Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-11 07:27 ` Re: NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
@ 2004-08-11 07:41 ` Michael Glaesemann <grzm@myrealbox.com>
2004-08-11 17:13 ` Re: NUMERIC x VARCHAR 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 05:42 NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 06:47 ` Re: NUMERIC x VARCHAR Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
2004-08-11 07:27 ` Re: NUMERIC x VARCHAR Er Galvão Abbott <galvao@galvao.eti.br>
2004-08-11 07:41 ` Re: NUMERIC x VARCHAR Michael Glaesemann <grzm@myrealbox.com>
@ 2004-08-11 17:13 ` Er Galvão Abbott <galvao@galvao.eti.br>
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ã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ã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>
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 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
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