21 bit number for sequence

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/

Actually what i want to do is store 100000000000000100000 as the maximum
value in sequence. Is there a way for it ?

Thanks,
Shoaib

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/

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

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

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

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

Thanks Dawid. I also used something similar to that kind of approach and its
working for me.

/Shoaib