pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedData Type to store Leading Zero(0)
14+ messages / 9 participants
[nested] [flat]
* Data Type to store Leading Zero(0)
@ 2021-01-20 11:13 soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-01-20 12:39 ` Re: Data Type to store Leading Zero(0) Thomas Kellerer <shammat@gmx.net>
2021-01-20 14:49 ` Re: Data Type to store Leading Zero(0) David G. Johnston <david.g.johnston@gmail.com>
2021-01-22 07:57 ` RE: Data Type to store Leading Zero(0) Michel SALAIS <msalais@msym.fr>
0 siblings, 4 replies; 14+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-01-20 11:13 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-01-20 11:20 ` Nidhi Gupta <nidhig631@gmail.com>
2021-01-20 11:29 ` RE: Data Type to store Leading Zero(0) Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
3 siblings, 2 replies; 14+ messages in thread
From: Nidhi Gupta @ 2021-01-20 11:20 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin
Use datatype smallint(1) for storing zero or use Boolean datatype.
On Wed, Jan 20, 2021, 4:43 PM <soumik.bhattacharjee@kpn.com> 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
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
@ 2021-01-20 11:29 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
2021-01-20 11:32 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
1 sibling, 1 reply; 14+ messages in thread
From: Kanninen Anssi EXT @ 2021-01-20 11:29 UTC (permalink / raw)
To: pgsql-admin
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 <nidhig631@gmail.com>
Sent: keskiviikko 20. tammikuuta 2021 13.20
To: soumik.bhattacharjee@kpn.com
Cc: pgsql-admin@postgresql.org
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 <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-01-20 11:29 ` RE: Data Type to store Leading Zero(0) Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
@ 2021-01-20 11:32 ` soumik.bhattacharjee@kpn.com
0 siblings, 0 replies; 14+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-01-20 11:32 UTC (permalink / raw)
To: Anssi.Kanninen@digia.com; pgsql-admin; nidhig631@gmail.com
Thanks , will test this both as need to check the entity mappings from my Java spring boot.
From: Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
Sent: woensdag 20 januari 2021 12:29
To: pgsql-admin@postgresql.org
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 <nidhig631@gmail.com<mailto:nidhig631@gmail.com>>
Sent: keskiviikko 20. tammikuuta 2021 13.20
To: soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@postgresql.org<mailto:pgsql-admin@postgresql.org>
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 <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
@ 2021-04-15 13:47 ` soumik.bhattacharjee@kpn.com
2021-04-15 13:53 ` Re: Data Type to store Leading Zero(0) David G. Johnston <david.g.johnston@gmail.com>
2021-04-15 13:54 ` Re: Data Type to store Leading Zero(0) Laurenz Albe <laurenz.albe@cybertec.at>
2021-04-15 15:04 ` Re: Data Type to store Leading Zero(0) Paul Smith <paul@pscs.co.uk>
2021-04-16 13:00 ` Re: Data Type to store Leading Zero(0) Imre Samu <pella.samu@gmail.com>
1 sibling, 4 replies; 14+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-04-15 13:47 UTC (permalink / raw)
To: nidhig631@gmail.com; +Cc: pgsql-admin
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)
Use datatype smallint(1) for storing zero or use Boolean datatype.
On Wed, Jan 20, 2021, 4:43 PM <soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com>> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-04-15 13:53 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2021-04-15 13:53 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin
On Thu, Apr 15, 2021 at 6:47 AM <soumik.bhattacharjee@kpn.com> wrote:
>
> - Boolean is not possible as this is phone number.
>
>
Phone numbers, despite the word "number" in the name, aren't numbers. Use
text for them. Or, in the off chance you truly find the performance hit to
be unacceptable (I doubt this to be the case) use a made-up integer-like
identifier as the PK but then store the actual phone number as a second
text field.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-04-15 13:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
3 siblings, 0 replies; 14+ messages in thread
From: Laurenz Albe @ 2021-04-15 13:54 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; nidhig631@gmail.com; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-04-15 15:04 ` Paul Smith <paul@pscs.co.uk>
2021-04-16 12:41 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
3 siblings, 1 reply; 14+ messages in thread
From: Paul Smith @ 2021-04-15 15:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 15/04/2021 14:47, soumik.bhattacharjee@kpn.com wrote:
>
> 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
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-04-15 15:04 ` Re: Data Type to store Leading Zero(0) Paul Smith <paul@pscs.co.uk>
@ 2021-04-16 12:41 ` soumik.bhattacharjee@kpn.com
0 siblings, 0 replies; 14+ messages in thread
From: soumik.bhattacharjee@kpn.com @ 2021-04-16 12:41 UTC (permalink / raw)
To: paul@pscs.co.uk; pgsql-admin@lists.postgresql.org
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)
On 15/04/2021 14:47, soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
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<mailto:support@pscs.co.uk> - 01484 855800
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates <http://www.pscs.co.uk/go/subscribe;
Thanks Paul for your broad example. Will test it with more scenarios and update here.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-04-16 13:00 ` Imre Samu <pella.samu@gmail.com>
2021-04-16 13:08 ` Re: Data Type to store Leading Zero(0) Imre Samu <pella.samu@gmail.com>
3 siblings, 1 reply; 14+ messages in thread
From: Imre Samu @ 2021-04-16 13:00 UTC (permalink / raw)
To: pgsql-admin
> Boolean is not possible as this is phone number.
related:
https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md
*"Phone numbers contain only digits*
*In Israel, certain advertising numbers start with a *. In New Zealand,
non-urgent traffic incidents can be reported by calling *555 from a mobile
phone. Alpha characters may also be used in phone numbers, such as in
1-800-Flowers."*
*Phone numbers are numbersNever try to store phone numbers as an int or any
other kind of numeric data type. You can't do arithmetic on them, and while
007, 07 and 7 are the same number they are not necessarily the same phone
number - in some countries a leading 0 is significant and forms part of the
number itself (see A leading zero in numbers formatted for domestic usage
can always be discarded when dialing from abroad). Moreover, a phone number
may contain other diallable characters (see Phone numbers contain only
digits) or an extension portion, dialled after waiting for a tone.Tip: Use
the library to parse and format numbers to handle this correctly for every
country.*
*"""*
<soumik.bhattacharjee@kpn.com> ezt írta (időpont: 2021. ápr. 15., Cs,
15:47):
> 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)
>
>
>
> Use datatype smallint(1) for storing zero or use Boolean datatype.
>
>
>
> On Wed, Jan 20, 2021, 4:43 PM <soumik.bhattacharjee@kpn.com> 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
>
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Re: Data Type to store Leading Zero(0) Nidhi Gupta <nidhig631@gmail.com>
2021-04-15 13:47 ` RE: Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-04-16 13:00 ` Re: Data Type to store Leading Zero(0) Imre Samu <pella.samu@gmail.com>
@ 2021-04-16 13:08 ` Imre Samu <pella.samu@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Imre Samu @ 2021-04-16 13:08 UTC (permalink / raw)
To: pgsql-admin
> Boolean is not possible as this is phone number.
related2. for inspiration:
*"A (partially implemented!) PostgreSQL extension that provides access to
Google's libphonenumber"*
https://github.com/blm768/pg-libphonenumber * "This extension is in an
alpha state." *
some special characters ( *,# ) not implemented:
https://github.com/blm768/pg-libphonenumber/issues/5
Imre
Imre Samu <pella.samu@gmail.com> ezt írta (időpont: 2021. ápr. 16., P,
15:00):
> > Boolean is not possible as this is phone number.
>
> related:
> https://github.com/google/libphonenumber/blob/master/FALSEHOODS.md
>
> *"Phone numbers contain only digits*
> *In Israel, certain advertising numbers start with a *. In New Zealand,
> non-urgent traffic incidents can be reported by calling *555 from a mobile
> phone. Alpha characters may also be used in phone numbers, such as in
> 1-800-Flowers."*
>
>
>
>
> *Phone numbers are numbersNever try to store phone numbers as an int or
> any other kind of numeric data type. You can't do arithmetic on them, and
> while 007, 07 and 7 are the same number they are not necessarily the same
> phone number - in some countries a leading 0 is significant and forms part
> of the number itself (see A leading zero in numbers formatted for domestic
> usage can always be discarded when dialing from abroad). Moreover, a phone
> number may contain other diallable characters (see Phone numbers contain
> only digits) or an extension portion, dialled after waiting for a tone.Tip:
> Use the library to parse and format numbers to handle this correctly for
> every country.*
> *"""*
>
> <soumik.bhattacharjee@kpn.com> ezt írta (időpont: 2021. ápr. 15., Cs,
> 15:47):
>
>> 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)
>>
>>
>>
>> Use datatype smallint(1) for storing zero or use Boolean datatype.
>>
>>
>>
>> On Wed, Jan 20, 2021, 4:43 PM <soumik.bhattacharjee@kpn.com> 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
>>
>>
>>
>>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-01-20 12:39 ` Thomas Kellerer <shammat@gmx.net>
3 siblings, 0 replies; 14+ messages in thread
From: Thomas Kellerer @ 2021-01-20 12:39 UTC (permalink / raw)
To: pgsql-admin; +Cc: soumik.bhattacharjee@kpn.com
soumik.bhattacharjee@kpn.com 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
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-01-20 14:49 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2021-01-20 14:49 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; +Cc: pgsql-admin
On Wed, Jan 20, 2021 at 4:13 AM <soumik.bhattacharjee@kpn.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.
>
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.
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: Data Type to store Leading Zero(0)
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
@ 2021-01-22 07:57 ` Michel SALAIS <msalais@msym.fr>
3 siblings, 0 replies; 14+ messages in thread
From: Michel SALAIS @ 2021-01-22 07:57 UTC (permalink / raw)
To: soumik.bhattacharjee@kpn.com; pgsql-admin
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@kpn.com <soumik.bhattacharjee@kpn.com>
Envoyé : mercredi 20 janvier 2021 12:14
À : 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 --> 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
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2021-04-16 13:08 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-20 11:13 Data Type to store Leading Zero(0) soumik.bhattacharjee@kpn.com
2021-01-20 11:20 ` Nidhi Gupta <nidhig631@gmail.com>
2021-01-20 11:29 ` Kanninen Anssi EXT <Anssi.Kanninen@digia.com>
2021-01-20 11:32 ` soumik.bhattacharjee@kpn.com
2021-04-15 13:47 ` soumik.bhattacharjee@kpn.com
2021-04-15 13:53 ` David G. Johnston <david.g.johnston@gmail.com>
2021-04-15 13:54 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-04-15 15:04 ` Paul Smith <paul@pscs.co.uk>
2021-04-16 12:41 ` soumik.bhattacharjee@kpn.com
2021-04-16 13:00 ` Imre Samu <pella.samu@gmail.com>
2021-04-16 13:08 ` Imre Samu <pella.samu@gmail.com>
2021-01-20 12:39 ` Thomas Kellerer <shammat@gmx.net>
2021-01-20 14:49 ` David G. Johnston <david.g.johnston@gmail.com>
2021-01-22 07:57 ` Michel SALAIS <msalais@msym.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox