Data Type to store Leading Zero(0)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Data Type to store Leading Zero(0)

soumik.bhattacharjee

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

 

Reply | Threaded
Open this post in threaded view
|

Re: Data Type to store Leading Zero(0)

Nidhi Gupta
Use datatype smallint(1) for storing zero or use Boolean datatype.

On Wed, Jan 20, 2021, 4:43 PM <[hidden email]> wrote:

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

 

Reply | Threaded
Open this post in threaded view
|

RE: Data Type to store Leading Zero(0)

Kanninen Anssi EXT

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

 

From: Nidhi Gupta <[hidden email]>
Sent: keskiviikko 20. tammikuuta 2021 13.20
To: [hidden email]
Cc: [hidden email]
Subject: Re: Data Type to store Leading Zero(0)

 

Use datatype smallint(1) for storing zero or use Boolean datatype.

 

On Wed, Jan 20, 2021, 4:43 PM <[hidden email]> wrote:

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

 

Reply | Threaded
Open this post in threaded view
|

RE: Data Type to store Leading Zero(0)

soumik.bhattacharjee

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

 

From: Kanninen Anssi EXT <[hidden email]>
Sent: woensdag 20 januari 2021 12:29
To: [hidden email]
Subject: RE: Data Type to store Leading Zero(0)

 

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

 

From: Nidhi Gupta <[hidden email]>
Sent: keskiviikko 20. tammikuuta 2021 13.20
To: [hidden email]
Cc: [hidden email]
Subject: Re: Data Type to store Leading Zero(0)

 

Use datatype smallint(1) for storing zero or use Boolean datatype.

 

On Wed, Jan 20, 2021, 4:43 PM <[hidden email]> wrote:

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

 

Reply | Threaded
Open this post in threaded view
|

Re: Data Type to store Leading Zero(0)

Thomas Kellerer-4
In reply to this post by soumik.bhattacharjee
[hidden email] schrieb am 20.01.2021 um 12:13:
> 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 aperformance impact if we define *varchar*
> in the data type.


While there is a performance impact between an integer and varchar, in my experience
it's rarely that big that it will become a problem.

Thomas


Reply | Threaded
Open this post in threaded view
|

Re: Data Type to store Leading Zero(0)

David G Johnston
In reply to this post by soumik.bhattacharjee
On Wed, Jan 20, 2021 at 4:13 AM <[hidden email]> wrote:

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.


A true number stores leading zeros since all bits in the representation are always present regardless of whether they are zero or one.  Thus it is a cosmetic presentation choice to suppress displaying those leading zeros, not a storage one.

If these aren't true numbers, i.e., "0001" and "000001" are actually different, then what you have is a string that only uses numbers.  Model that using text and let the performance fall where it will.

David J.

Reply | Threaded
Open this post in threaded view
|

RE: Data Type to store Leading Zero(0)

Michel SALAIS
In reply to this post by soumik.bhattacharjee

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 : [hidden email] <[hidden email]>
Envoyé : mercredi 20 janvier 2021 12:14
À : [hidden email]
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