Thread: Dumb question about serial's upper limit

Dumb question about serial's upper limit

From
CSN
Date:
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/

Re: Dumb question about serial's upper limit

From
Tom Lane
Date:
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

Re: Dumb question about serial's upper limit

From
CSN
Date:
--- 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

Re: Dumb question about serial's upper limit

From
Michael Glaesemann
Date:
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




Re: Dumb question about serial's upper limit

From
"Jim C. Nasby"
Date:
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

Re: Dumb question about serial's upper limit

From
Tom Lane
Date:
"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

Re: Dumb question about serial's upper limit

From
Michael Glaesemann
Date:
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




Re: Dumb question about serial's upper limit

From
"Dann Corbit"
Date:
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

Re: Dumb question about serial's upper limit

From
"Jim C. Nasby"
Date:
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

Re: Dumb question about serial's upper limit

From
"Jim C. Nasby"
Date:
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