From: Paul Smith <paul@pscs.co.uk>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Data Type to store Leading Zero(0)
Date: Thu, 15 Apr 2021 16:04:53 +0100
Message-ID: <546d0cf6-1927-4ec9-b757-734fb58f1a0f@pscs.co.uk> (raw)
In-Reply-To: <7bb92c536d544e818562bfe713eac363@kpn.com>
References: <c77968e8e35a4e9d8f68c2683ae1b807@kpn.com>
<CAAbUf+FEtZC6g6J_sM9XqkmRtJcTHwfTCQbDBmqF5WM__1aTbw@mail.gmail.com>
<7bb92c536d544e818562bfe713eac363@kpn.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: paul@pscs.co.uk, pgsql-admin@lists.postgresql.org
Subject: Re: Data Type to store Leading Zero(0)
In-Reply-To: <546d0cf6-1927-4ec9-b757-734fb58f1a0f@pscs.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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