From: soumik.bhattacharjee@kpn.com
To: paul@pscs.co.uk
To: pgsql-admin@lists.postgresql.org
Subject: RE: Data Type to store Leading Zero(0)
Date: Fri, 16 Apr 2021 12:41:10 +0000
Message-ID: <aeabd37f930b4432851c98f10f9e943f@kpn.com> (raw)
In-Reply-To: <546d0cf6-1927-4ec9-b757-734fb58f1a0f@pscs.co.uk>
References: <c77968e8e35a4e9d8f68c2683ae1b807@kpn.com>
<CAAbUf+FEtZC6g6J_sM9XqkmRtJcTHwfTCQbDBmqF5WM__1aTbw@mail.gmail.com>
<7bb92c536d544e818562bfe713eac363@kpn.com>
<546d0cf6-1927-4ec9-b757-734fb58f1a0f@pscs.co.uk>
From: Paul Smith <paul@pscs.co.uk>
Sent: donderdag 15 april 2021 17:05
To: pgsql-admin@lists.postgresql.org
Subject: Re: Data Type to store Leading Zero(0)
On 15/04/2021 14:47, soumik.bhattacharjee@kpn.com<mailto: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<mailto:support@pscs.co.uk> - 01484 855800
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates <http://www.pscs.co.uk/go/subscribe;
Thanks Paul for your broad example. Will test it with more scenarios and update here.
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: soumik.bhattacharjee@kpn.com, paul@pscs.co.uk, pgsql-admin@lists.postgresql.org
Subject: RE: Data Type to store Leading Zero(0)
In-Reply-To: <aeabd37f930b4432851c98f10f9e943f@kpn.com>
* 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