Subject: Re: Data Type to store Leading Zero(0)
To: pgsql-admin@lists.postgresql.org
References: <c77968e8e35a4e9d8f68c2683ae1b807@kpn.com>
 <CAAbUf+FEtZC6g6J_sM9XqkmRtJcTHwfTCQbDBmqF5WM__1aTbw@mail.gmail.com>
 <7bb92c536d544e818562bfe713eac363@kpn.com>
From: Paul Smith <paul@pscs.co.uk>
Message-ID: <546d0cf6-1927-4ec9-b757-734fb58f1a0f@pscs.co.uk>
Date: Thu, 15 Apr 2021 16:04:53 +0100
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0)
 Gecko/20100101 Thunderbird/78.8.1
MIME-Version: 1.0
In-Reply-To: <7bb92c536d544e818562bfe713eac363@kpn.com>
Content-Type: multipart/alternative;
 boundary="------------EAC786C9AD89620B56884D7D"
Content-Language: en-GB
Archived-At: 
 <https://www.postgresql.org/message-id/546d0cf6-1927-4ec9-b757-734fb58f1a0f%40pscs.co.uk>
Precedence: bulk

This is a multi-part message in MIME format.
--------------EAC786C9AD89620B56884D7D
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

On 15/04/2021 14:47, soumik.bhattacharjee@kpn.com wrote:
>
> Thanks for your mail.
>
>   * Boolean is not possible as this is phone number.
>
Phone numbers aren't numbers. They're text. Does it make sense to add 
two phone numbers together? No? Then they're not numbers :-)

So, either store them as varchar or do something fancy.

For instance, you could store them as an bigint (an integer is probably 
not big enough), with a smallint indicating how many preceding zeros 
there are - but I'd expect a varchar to be quicker, and it'd certainly 
be simpler.

You say that 'varchar' gives a performance hit - can you explain how? 
Have you done some performance profiling, or have you just assumed?


If the performance hit is with searching, then you could index on the 
phone number cast to an integer, and then do a string comparison to verify?

Eg

create table people (name varchar, phone varchar);

create index people_phone on people(cast(phone as bigint));

select * from people where cast('07000123456' as bigint)= cast(phone as 
bigint) and '07000123456' = phone;

this will use the 'people_phone' index, so searches will be quick.


If 'bigint' indexes are too slow, then you could just index the last 9 
digits and verify with a string compare:

create index people_phone on people(cast(substring(phone from 
length(phone) - 8) as integer));

select * from people where cast('000123456' as integer) = 
cast(substring(phone from length(phone) - 8) as integer) and 
'07000123456' = phone;

-- 
Paul
Paul Smith Computer Services
support@pscs.co.uk - 01484 855800


-- 


Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53

Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
--------------EAC786C9AD89620B56884D7D
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 15/04/2021 14:47,
      <a class="moz-txt-link-abbreviated" href="mailto:soumik.bhattacharjee@kpn.com">soumik.bhattacharjee@kpn.com</a> wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:7bb92c536d544e818562bfe713eac363@kpn.com">
      <meta name="Generator" content="Microsoft Word 15 (filtered
        medium)">
      <div class="WordSection1">
        <p class="MsoNormal"><span lang="EN-GB">Thanks for your mail.</span></p>
        <ul type="disc">
          <li class="MsoListParagraph"><span lang="EN-GB">Boolean is not
              possible as this is phone number.</span></li>
        </ul>
      </div>
    </blockquote>
    Phone numbers aren't numbers. They're text. Does it make sense to
    add two phone numbers together? No? Then they're not numbers :-)<br>
    <br>
    <p>So, either store them as varchar or do something fancy.</p>
    <p>For instance, you could store them as an bigint (an integer is
      probably not big enough), with a smallint indicating how many
      preceding zeros there are - but I'd expect a varchar to be
      quicker, and it'd certainly be simpler.<br>
    </p>
    <p>You say that 'varchar' gives a performance hit - can you explain
      how? Have you done some performance profiling, or have you just
      assumed?<br>
    </p>
    <p><br>
      <br>
      If the performance hit is with searching, then you could index on
      the phone number cast to an integer, and then do a string
      comparison to verify?<br>
      <br>
      Eg</p>
    <p>create table people (name varchar, phone varchar);</p>
    <p>create index people_phone on people(cast(phone as bigint));<br>
    </p>
    <p>select * from people where cast('07000123456' as bigint)=
      cast(phone as bigint) and '07000123456' = phone;<br>
      <br>
      this will use the 'people_phone' index, so searches will be quick.</p>
    <p><br>
      If 'bigint' indexes are too slow, then you could just index the
      last 9 digits and verify with a string compare:<br>
      <br>
      create index people_phone on people(cast(substring(phone from
      length(phone) - 8) as integer));</p>
    <p>select * from people where cast('000123456' as integer) =
      cast(substring(phone from length(phone) - 8) as integer) and
      '07000123456' = phone;<br>
      <br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Paul
Paul Smith Computer Services
<a class="moz-txt-link-abbreviated" href="mailto:support@pscs.co.uk">support@pscs.co.uk</a> - 01484 855800</pre>
  <div><br />
	
	</div>

<div>-- </div>

<div><br />
	
	</div>

<div><span style="font-weight: bold;">Paul Smith Computer Services</span></div>

<div>Tel: 01484 855800</div>

<div>Vat No: GB 685 6987 53</div>

<div><br />
	
	</div>

<div><a href="http://www.pscs.co.uk/go/subscribe">Sign up for news &amp; updates </a></div></body>
</html>


--------------EAC786C9AD89620B56884D7D--