RE: Data Type to store Leading Zero(0)

From: Paul Smith <paul(at)pscs(dot)co(dot)uk>
Sent: donderdag 15 april 2021 17:05
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: Data Type to store Leading Zero(0)

On 15/04/2021 14:47, soumik(dot)bhattacharjee(at)kpn(dot)com<mailto:soumik(dot)bhattacharjee(at)kpn(dot)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(at)pscs(dot)co(dot)uk<mailto:support(at)pscs(dot)co(dot)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.