Postgres Pro
Downloads
Home   >   mailing lists

Thread: 21 bit number for sequence

21 bit number for sequence

From
"Shoaib Mir"
Date:
Right now Sequence by default uses bigint which supports upto 19bit numbers but I want to use a 21bit that can be supported by NUMERIC datatype. Is there any way I can sepcify while creating a sequence what datatype to use or if I can specify to create sequence with numeric datatype?

/Shoaib

Re: 21 bit number for sequence

From
Martijn van Oosterhout
Date:
On Sat, Apr 15, 2006 at 10:24:59AM +0500, Shoaib Mir wrote:
> Right now Sequence by default uses bigint which supports upto 19bit numbers
> but I want to use a 21bit that can be supported by NUMERIC datatype. Is
> there any way I can sepcify while creating a sequence what datatype to use
> or if I can specify to create sequence with numeric datatype?

What do you mean by 19bit? Normal ints support upto 31 bits and bigints
upto 2^63 positive numbers.

Maybe you mean 19 *digit* numbers, which is about what a bigint can do.
Well, that's more numbers than you're ever likely to generate so that
question become: why do you need a sequence with that kind of range?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

Re: 21 bit number for sequence

From
"Shoaib Mir"
Date:
Actually what i want to do is store 100000000000000100000 as the maximum value in sequence. Is there a way for it ?

Thanks,
Shoaib

On 4/15/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Sat, Apr 15, 2006 at 10:24:59AM +0500, Shoaib Mir wrote:
> Right now Sequence by default uses bigint which supports upto 19bit numbers
> but I want to use a 21bit that can be supported by NUMERIC datatype. Is
> there any way I can sepcify while creating a sequence what datatype to use
> or if I can specify to create sequence with numeric datatype?

What do you mean by 19bit? Normal ints support upto 31 bits and bigints
upto 2^63 positive numbers.

Maybe you mean 19 *digit* numbers, which is about what a bigint can do.
Well, that's more numbers than you're ever likely to generate so that
question become: why do you need a sequence with that kind of range?

Have a nice day,
--
Martijn van Oosterhout   < kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEQKWlIB7bNG8LQkwRAtp7AJ9365tVEPUqlS8Uks2RpoKebe6vyQCfcWSJ
h+6EyHJOjWa4QIoFRxtu+SQ=
=Jpg3
-----END PGP SIGNATURE-----



Re: 21 bit number for sequence

From
Martijn van Oosterhout
Date:
On Sat, Apr 15, 2006 at 12:52:49PM +0500, Shoaib Mir wrote:
> Actually what i want to do is store 100000000000000100000 as the maximum
> value in sequence. Is there a way for it ?

Is that number in binary or decimal? In binary it's easy because it's
only 1048608 decimal. In decimal it would require 66 bits, which
doesn't fit. The is still: why do you want a *sequence* to go that
high? A sequence starts counting a 1 and goes up until the limit. At
one count per second you'd take several million million years to get
though. Bigint indeed only goes upto 9223372036854775807.

If you just want to store numbers, use numeric. Why do you want to
combine numeric and a sequence?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

Re: 21 bit number for sequence

From
"Shoaib Mir"
Date:
Actually that is the application requirment to use 21 bit numbers as porting it from Oracle where it used to work.

Yeah now i have decided to use a numeric data type in a table and use that to write my own nextval and currval functions for that purpose.

Thanks for the help.

/Shoaib

On 4/15/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
On Sat, Apr 15, 2006 at 12:52:49PM +0500, Shoaib Mir wrote:
> Actually what i want to do is store 100000000000000100000 as the maximum
> value in sequence. Is there a way for it ?

Is that number in binary or decimal? In binary it's easy because it's
only 1048608 decimal. In decimal it would require 66 bits, which
doesn't fit. The is still: why do you want a *sequence* to go that
high? A sequence starts counting a 1 and goes up until the limit. At
one count per second you'd take several million million years to get
though. Bigint indeed only goes upto 9223372036854775807.

If you just want to store numbers, use numeric. Why do you want to
combine numeric and a sequence?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEQKhuIB7bNG8LQkwRAkOpAJ0cRCABC2OmbDDVqNcPuAT0WjADdQCcDkGq
dzC7ykqq/h6YhWpgDJIQ0kk=
=YZzx
-----END PGP SIGNATURE-----



Re: 21 bit number for sequence

From
Christian Kratzer
Date:
Hi,

On Sat, 15 Apr 2006, Shoaib Mir wrote:

> Actually that is the application requirment to use 21 bit numbers as porting
> it from Oracle where it used to work.

21 bits are no problem as bigints have 64 bits.  If you mean decimal
digits please explicitly say so. A bit is a binary digit.

> Yeah now i have decided to use a numeric data type in a table and use that
> to write my own nextval and currval functions for that purpose.

you could also try to fix you application which most certainly is
severly broken if it tries to store specific numbers in a sequence.

Greetings
Christian

--
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136

Re: 21 bit number for sequence

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> If you just want to store numbers, use numeric. Why do you want to
> combine numeric and a sequence?

He could use a numeric column and write the default as
    nextval('seq')::numeric

Of course, he'll be paying through the nose performance-wise for
his insistence on not fixing his Oracle-centric data representation,
but if programmer time is cheaper than machine time then maybe it's
the right tradeoff.

            regards, tom lane

Re: 21 bit number for sequence

From
"Dawid Kuroczko"
Date:
On 4/15/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
Actually that is the application requirment to use 21 bit numbers as porting it from Oracle where it used to work.

Yeah now i have decided to use a numeric data type in a table and use that to write my own nextval and currval functions for that purpose.


Something like:
CREATE SEQUENCE some_id_seq;
CREATE TABLE foo (
    some_id bit(21) DEFAULT nextval('some_id_seq')::bit(21),
    t text
);
insert into foo(t) values ('a');
insert into foo(t) values ('bar');
insert into foo(t) values ('baz');
insert into foo(some_id,t) values ('100000000000000100000', 'uhh');
SELECT * FROM foo;
       some_id         t
--------------------- ---
000000000000000000001 a
000000000000000000010 bar
000000000000000000011 baz
100000000000000100000 uhh
(4 rows)

HTH, HAND. :)
   Regards,
         Dawid

Re: 21 bit number for sequence

From
"Shoaib Mir"
Date:
Thanks Dawid. I also used something similar to that kind of approach and its working for me.

/Shoaib

On 4/18/06, Dawid Kuroczko < qnex42@gmail.com> wrote:
On 4/15/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
Actually that is the application requirment to use 21 bit numbers as porting it from Oracle where it used to work.

Yeah now i have decided to use a numeric data type in a table and use that to write my own nextval and currval functions for that purpose.


Something like:
CREATE SEQUENCE some_id_seq;
CREATE TABLE foo (
    some_id bit(21) DEFAULT nextval('some_id_seq')::bit(21),
    t text
);
insert into foo(t) values ('a');
insert into foo(t) values ('bar');
insert into foo(t) values ('baz');
insert into foo(some_id,t) values ('100000000000000100000', 'uhh');
SELECT * FROM foo;
       some_id         t
--------------------- ---
000000000000000000001 a
000000000000000000010 bar
000000000000000000011 baz
100000000000000100000 uhh
(4 rows)

HTH, HAND. :)
   Regards,
         Dawid