RE: Data Type to store Leading Zero(0)

["Michel SALAIS" <msalais@xxxxxxx>]

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

 

Michel SALAIS

De : soumik.bhattacharjee@xxxxxxx <soumik.bhattacharjee@xxxxxxx>
Envoyé : mercredi 20 janvier 2021 12:14
À : pgsql-admin@xxxxxxxxxxxxxx
Objet : 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