Thread: Dumb question about serial's upper limit
If integer's range is -2147483648 to +2147483647, why is serial's range only 1 to 2147483647 instead of 1 to about 4294967294? CSN __________________________________ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/
CSN <cool_screen_name90001@yahoo.com> writes:
> If integer's range is -2147483648 to +2147483647, why
> is serial's range only 1 to 2147483647 instead of 1 to
> about 4294967294?
How are you going to stuff 4294967294 into an integer field, which as
you just stated has an upper limit of 2147483647?
If we had an unsigned int type, we could use it for serial and get
that result, but we do not.
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > CSN <cool_screen_name90001@yahoo.com> writes: > > If integer's range is -2147483648 to +2147483647, > why > > is serial's range only 1 to 2147483647 instead of > 1 to > > about 4294967294? > > How are you going to stuff 4294967294 into an > integer field, which as > you just stated has an upper limit of 2147483647? > > If we had an unsigned int type, we could use it for > serial and get > that result, but we do not. > > regards, tom lane > I was thinking about the types in the C code behind PostgreSQL, rather than types in PG itself. Been a long time since I coded in C but I thought it had unsigned ints and maybe data types could be mapped as so (pardon my ignorance about C/PG's inner workings): PG int => C signed int PG serial => C unsigned int Anyhow, was just something I was curious about. CSN __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Oct 11, 2005, at 14:04 , CSN wrote:
> I was thinking about the types in the C code behind
> PostgreSQL, rather than types in PG itself. Been a
> long time since I coded in C but I thought it had
> unsigned ints and maybe data types could be mapped as
> so (pardon my ignorance about C/PG's inner workings):
>
> PG int => C signed int
> PG serial => C unsigned int
Serial is not a datatype per se; it's essentially a macro to create a
sequence (foo_seq) and an INT column that has a default value of
nextval('foo_seq').
Michael Glaesemann
grzm myrealbox com
On Mon, Oct 10, 2005 at 10:59:03PM -0400, Tom Lane wrote: > CSN <cool_screen_name90001@yahoo.com> writes: > > If integer's range is -2147483648 to +2147483647, why > > is serial's range only 1 to 2147483647 instead of 1 to > > about 4294967294? > > How are you going to stuff 4294967294 into an integer field, which as > you just stated has an upper limit of 2147483647? > > If we had an unsigned int type, we could use it for serial and get > that result, but we do not. Out of curiosity... why don't we have unsigned ints? I for one would certainly use them for id fields, as well as some other places where I knew negative numbers weren't valid. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Out of curiosity... why don't we have unsigned ints?
Quick, is 42 an int or an unsigned int?
I think it'd create a slew of new ambiguous cases in the
numeric-datatype hierarchy, for what is really pretty darn small gain.
We're already just barely getting by the problem that 42 might be
intended as an int2 or int8 constant --- and at least those three
datatypes have compatible comparison semantics, so that there aren't any
fundamental semantic problems created if you decide that a constant is
one or the other. Adding unsigned types to the mix seems to me to be
likely to cause some serious issues.
But feel free to give it a try, if you think it's worth a nontrivial
amount of work.
regards, tom lane
On Oct 11, 2005, at 15:12 , Jim C. Nasby wrote: > Out of curiosity... why don't we have unsigned ints? I for one would > certainly use them for id fields, as well as some other places where I > knew negative numbers weren't valid. Check the archives. I know this has come up a number of times in the past, but don't recall the reasons off the top of my head. Michael Glaesemann grzm myrealbox com
How about something like: CREATE DOMAIN unsigned_small AS smallint check (VALUE >= 0) CREATE DOMAIN unsigned_int AS integer check (VALUE >= 0) CREATE DOMAIN unsigned_big AS bigint check (VALUE >= 0) The objection might be that we lose one bit of field width. But the extra safety is probably worth it if you really need unsigned and want to avoid ambiguity. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Jim C. Nasby > Sent: Monday, October 10, 2005 11:12 PM > To: Tom Lane > Cc: CSN; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Dumb question about serial's upper limit > > On Mon, Oct 10, 2005 at 10:59:03PM -0400, Tom Lane wrote: > > CSN <cool_screen_name90001@yahoo.com> writes: > > > If integer's range is -2147483648 to +2147483647, why > > > is serial's range only 1 to 2147483647 instead of 1 to > > > about 4294967294? > > > > How are you going to stuff 4294967294 into an integer field, which as > > you just stated has an upper limit of 2147483647? > > > > If we had an unsigned int type, we could use it for serial and get > > that result, but we do not. > > Out of curiosity... why don't we have unsigned ints? I for one would > certainly use them for id fields, as well as some other places where I > knew negative numbers weren't valid. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
On Mon, Oct 10, 2005 at 11:52:40PM -0700, Dann Corbit wrote: > How about something like: > > CREATE DOMAIN unsigned_small AS smallint check (VALUE >= 0) > > CREATE DOMAIN unsigned_int AS integer check (VALUE >= 0) > > CREATE DOMAIN unsigned_big AS bigint check (VALUE >= 0) > > The objection might be that we lose one bit of field width. > But the extra safety is probably worth it if you really need unsigned > and want to avoid ambiguity. Certainly. But I was more interested in the space. :P -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Tue, Oct 11, 2005 at 02:22:23AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > Out of curiosity... why don't we have unsigned ints? > > Quick, is 42 an int or an unsigned int? > > I think it'd create a slew of new ambiguous cases in the > numeric-datatype hierarchy, for what is really pretty darn small gain. > We're already just barely getting by the problem that 42 might be > intended as an int2 or int8 constant --- and at least those three > datatypes have compatible comparison semantics, so that there aren't any > fundamental semantic problems created if you decide that a constant is > one or the other. Adding unsigned types to the mix seems to me to be > likely to cause some serious issues. Couldn't the same logic of starting with the most restrictive case and working up work here as well? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461