Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l2rKA-0004I6-MT for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jan 2021 07:57:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1l2rK7-0005ro-Hq for pgsql-admin@arkaria.postgresql.org; Fri, 22 Jan 2021 07:57:39 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l2rK7-0005rh-5w for pgsql-admin@lists.postgresql.org; Fri, 22 Jan 2021 07:57:39 +0000 Received: from mout.kundenserver.de ([212.227.17.13]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1l2rK0-0000Fz-C7 for pgsql-admin@postgresql.org; Fri, 22 Jan 2021 07:57:38 +0000 Received: from ANAT ([78.197.128.121]) by mrelayeu.kundenserver.de (mreue108 [212.227.15.184]) with ESMTPSA (Nemesis) id 1N7zNt-1m6Z1Q2Dqt-0153zp; Fri, 22 Jan 2021 08:57:28 +0100 From: "Michel SALAIS" To: , References: In-Reply-To: Subject: RE: Data Type to store Leading Zero(0) Date: Fri, 22 Jan 2021 08:57:25 +0100 Message-ID: <02df01d6f094$3a7301d0$af590570$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_02E0_01D6F09C.9C3A01E0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQKNdCPUbaltLFscfw0y/1LwWlxqsKjGO7qg Content-Language: fr X-Provags-ID: V03:K1:3+ayUvD8TvMhjq0HIOoO0mhIVbp5PpTJAT5L8S5vEabbb9JZ+0G 6y9GjjZYGQB++AjfuJQND9TWFjuzR14FquuqkOBdp3pB6VEeBVw9XdGhppqCi5w97aFL9QK hHq8qbSMfLPQgnmrvKEWO1ainAnnwydtVegyAWWmuSdBOEv2nKPEBhOQ+q/es3BBbUw8PvR ldkqZ5z1UE0+4hpe0BOQQ== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:vbrLbK3U4Wc=:DvRkqrVklnbuuF/KX0Z4TU mlItK7wTvWiejIbJp379Tj/VhtVqOlG2Jdsm5/BMcLhIR6P+41nAoQRr79uIpXvWvu9n3RQ+U Gs6eeVslwxGh7G4Ui9elDJUanoH7Z8yLTfzV1UydILgzKyZ1fbLa9tuxMY8K4gGpUrNbsO5yp hgZf6uBmFZzf5ijaXpfWtDjus7bdqLa9M6WAe2sY//pCCbzu/Fv1FiByz4vu72LCrCSaG5Hmc 0WTEUi+Y4AiDSDmnvwn1oVjrKhJkVMr9lFxMuRgnM4LrSh6+sgftskQ9a3Sezyowf+E0IKhQV YiIxP5bbj/FMht93bwCTERfRnbJqB39BiE5l+i/xC8ENvfwi8rUtyeWci6mbR3Q1qmw0ypiM6 gfNgwYJx/QsMQ9RMIN4HdqMQ0Pj6qjEjtItVynXJ2IDIrhL8MkGlxBYSqwUkt List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_02E0_01D6F09C.9C3A01E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 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=92s 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, =85 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 =20 Regards =20 Michel SALAIS De : soumik.bhattacharjee@kpn.com =20 Envoy=E9 : mercredi 20 janvier 2021 12:14 =C0 : pgsql-admin@postgresql.org Objet : Data Type to store Leading Zero(0) =20 Hello Experts, =20 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. =20 We are not able to use numeric data type as it trims the Zero value in leading, but it=92s also a performance impact if we define varchar in = the data type. =20 Please suggest. =20 Thanks =20 ------=_NextPart_000_02E0_01D6F09C.9C3A01E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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

---------

=A0 = 0012

 

Regards

 

Michel = SALAIS

De : soumik.bhattacharjee@kpn.com = <soumik.bhattacharjee@kpn.com>
Envoy=E9 : mercredi = 20 janvier 2021 12:14
=C0 : = pgsql-admin@postgresql.org
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 =E0  = 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

 

------=_NextPart_000_02E0_01D6F09C.9C3A01E0--