Why doesn't PostgreSQL use unsigned integers for IDs? Wouldn't that give twice as many possible records?

Question

According to this documentation, PostgreSQL doesn't support unsigned integers.

While I get that it makes the type resolution system less complicated, I don't see how this is practical for let's say auto-incrementing IDs.

Wouldn't adding an unsigned integer as an auto-increment ID result in a growth of twice the amount of possible records using a regular int? (4294967296 instead of 2147483648)

I know that it only means the difference between 1 bit, but it's still a bit that you will never use at this point.

Thanks!

Use negative IDs if you need the full range. No problem with that. — Laurenz Albe, Jan 23 '19 at 23:30
`serial` is (in effect) a macro which sets the auto-increment start point at 1, but you could establish your own sequence and specify the start point. See this previous answer: https://stackoverflow.com/a/13187557/2067753 — Paul Maxwell, Jan 23 '19 at 23:50

score 2 · Accepted Answer · answered Jan 24 '19 at 01:13

2

By convention IDs are positive integers commencing at 1 (& to my limited knowledge this isn't enforced by standards but would not be utterly surprised if this was so). The PostgreSQL serial data type implements this convention and the values a "auto generated" as well.

If you really wish to implement your own approach, you can do so like this:

create sequence epictable_seq
    MINVALUE -2147483648 
    start -2147483648 
    increment 1
    NO MAXVALUE
    CACHE 1;

✓

CREATE TABLE epictable
(
    mytable_key    INT unique not null,
    moobars        VARCHAR(40) not null,
    foobars        DATE
);

✓

insert into epictable(mytable_key, moobars,foobars) 
values
  (nextval('epictable_seq'),'delicious moobars','2012-05-01')
, (nextval('epictable_seq'),'worldwide interblag','2012-05-02')
;

2 rows affected

select * 
from epictable
;

mytable_key | moobars             | foobars   
----------: | :------------------ | :---------
-2147483648 | delicious moobars   | 2012-05-01
-2147483647 | worldwide interblag | 2012-05-02

db<>fiddle here

Also see this former answer

answered Jan 24 '19 at 01:13

Paul Maxwell

33,002
3
32
51

1

Thanks for replying, but I was wondering why this is the case rather than how to solve it. I currently do not have issues with the number of records I got, if I did I would just switch to 64-bit integers. I'm just curious what the thought process behind this was. – Peter Willemsen Jan 24 '19 at 02:00
2

"Starting at 1". As I said, I believe it is simply "by convention". I believe this has its roots in the way we learn to count when there is actually something visible to us to count, consequently our mental model for counting usually starts at 1. – Paul Maxwell Jan 24 '19 at 03:06
@PeterWillemsen Also, if you are worried that a 4 byte integer might run out of positive values, you could just use `bigserial`. I guess few people mind 4 bytes being wasted these days. If anybody is, it would not be difficult to add an unsigned integer data type to PostgreSQL, extensible as it is. – Laurenz Albe Jan 24 '19 at 05:04
@LaurenzAlbe isn't bigserial also signed? But yeah I get what you mean – Peter Willemsen Jan 24 '19 at 15:56
@Used_By_Already that doesn't make sense to me, forgive me if I sound naive here, but you can easily "start at 1" when you have an unsigned integer? – Peter Willemsen Jan 24 '19 at 15:57
@PeterWillemsen Yes, `bigint` is signed. But you have more than enough IDs that way even if you only use positive ones. – Laurenz Albe Jan 24 '19 at 16:17
If you start counting at 1 all negative values will be ignored. I.e. Starting at 1 and incrementing by 1 results in only positive values. Seems clear to me. – Paul Maxwell Jan 24 '19 at 19:59
@Used_By_Already the thing is that with an unsigned integer everything has to be positive or zero. I fail to see how something like bigserial would be superior to that. I'm used to, when programming C, for instance, that if you only need positive integers you can use unsigned ints. For instance, if I need the number 0 to 200 I can use uint8_t, which still uses 1 byte, except I can now use up to 256 instead of 128 (and I would never need the negative side anyway). – Peter Willemsen Jan 24 '19 at 20:53
1

Ah, maybe I now see the point of your question. I am not aware of any RDBMS that supports an unsigned integer (*although I don't know every rdbms out there*). As any numeric data type can be used in calculations I suspect it would just be too restrictive to only permit positives. – Paul Maxwell Jan 24 '19 at 23:18

Why doesn't PostgreSQL use unsigned integers for IDs? Wouldn't that give twice as many possible records?

1 Answers1