Thread: Data Type to store Leading Zero(0)

Hello Experts,

As per business needs we need to store Zero(0) in primary key column of table  with this kind of value à  07******** with applications written in Java microservices.

We are not able to use numeric data type as it trims the Zero value in leading, but it’s also a performance impact if we define varchar in the data type.

Please suggest.

Thanks

Hi,

How about storing the actual number in TEXT column and adding a separate INT primary key column where you either copy the number or use it as a generated column?

Or, if you actually want to include the leading zeroes in the primary key, how about two-column primary key like this:

number_of_leading_zeroes  smallint,
the_number                                integer

So, the number "0005556879" would become a primary key (3, 5556879).

Sincerely,

Anssi Kanninen

Thanks , will test this both as need to check the entity mappings from my Java spring boot.

Hi,

How about storing the actual number in TEXT column and adding a separate INT primary key column where you either copy the number or use it as a generated column?

Or, if you actually want to include the leading zeroes in the primary key, how about two-column primary key like this:

number_of_leading_zeroes  smallint,
the_number                                integer

So, the number "0005556879" would become a primary key (3, 5556879).

Sincerely,

Anssi Kanninen

Hi,

First of all, if you consider 0001 and 00001 as different values then you have two solution

• the solution proposed by Kanninen
• using string values and the column should be defined as varchar (or text)

If 0001 and 00001 are considered the same or let’s say that it is impossible to have these two values in your primary key then the question is simply a representation one and the choice should be

• numeric, int, bigint, … whatever convenient of these types
• Use of to_char() with appropriate format when displaying theses values. This lets you specify that leading zeros must be displayed like this

select to_char(12, '0999');

to_char

---------

  0012

Regards

Hello Experts,

As per business needs we need to store Zero(0) in primary key column of table  with this kind of value à  07******** with applications written in Java microservices.

We are not able to use numeric data type as it trims the Zero value in leading, but it’s also a performance impact if we define varchar in the data type.

Please suggest.

Thanks

Thanks for your mail.

• smallint will be out of range for this  due to number of bytes and ideally its behaving same way as any ***int data type.
• Boolean is not possible as this is phone number.

Please suggest

Thanks..

From: Nidhi Gupta <nidhig631@gmail.com>
Sent: woensdag 20 januari 2021 12:20
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Data Type to store Leading Zero(0)

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)

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