Message-ID: <770d9e620ade5e492c9aa4545c8e182cd6526884.camel@cybertec.at>
Subject: Re: Data Type to store Leading Zero(0)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: soumik.bhattacharjee@kpn.com, nidhig631@gmail.com
Cc: pgsql-admin@postgresql.org
Date: Thu, 15 Apr 2021 15:54:29 +0200
In-Reply-To: <7bb92c536d544e818562bfe713eac363@kpn.com>
References: <c77968e8e35a4e9d8f68c2683ae1b807@kpn.com>
	 <CAAbUf+FEtZC6g6J_sM9XqkmRtJcTHwfTCQbDBmqF5WM__1aTbw@mail.gmail.com>
	 <7bb92c536d544e818562bfe713eac363@kpn.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/770d9e620ade5e492c9aa4545c8e182cd6526884.camel%40cybertec.at>
Precedence: bulk

Nidhi Gupta <nidhig631@gmail.com> 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.


Use "integer" and define a view that pads the column with zeros on the
left side:

CREATE VIEW myview AS
SELECT lpad(if::text, 10, '0') AS id,
       othercolumn,
       ...
FROM mytable;

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com