9

I have two tables in PostgreSQL. The first one should have an auto-incrementing ID field that the second one references:

CREATE TABLE tableA (id SERIAL NOT NULL PRIMARY KEY, ...)
CREATE TABLE tableB (parent INTEGER NOT NULL REFERENCES tableA(id), ...)

According to documentation, SERIAL acts as unsigned 4-byte integer while INTEGER is signed:

serial      4 bytes     autoincrementing integer    1 to 2147483647
integer     4 bytes     typical choice for integer  -2147483648 to +2147483647

If I understand correctly, the data types that I have used are not compatible, but PostgreSQL apparently lacks unsigned integers. I know I probably won't use more than 2*10^9 IDs (and if I did, I could always use BIGSERIAL), and it's not all that important, but it seems a bit unclean to me to have signed integer reference an unsigned one. I am sure there must be a better way - am I missing something?

johndodo
  • 17,247
  • 15
  • 96
  • 113
  • There's an extension to handle unsigned integers: https://github.com/petere/pguint - it's referenced from the question you linked. – Aleks G Jan 01 '18 at 21:19
  • Yes, I know, thank you - but while I would prefer it if PostgreSQL supported unsigned integers, it's not _that_ important to me. Using extensions for that seems like an overkill in my case (I don't know the extension code maturity, it would introduce additional deployment constraints, could lead to future compatibility problems with core psql and similar). – johndodo Jan 01 '18 at 21:43

1 Answers1

14

A serial is an integer and it's not "unsigned". The sequence that is created automatically just happens to start at 1 - that's all. The column's data type is still an integer (you could make the sequence start at -2147483648 if you wanted to).

Quote from the manual

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to 

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

(emphasis mine)

  • Ah... I see my mistake now. When I saw that `SERIAL` starts at `1` I misread the end value as 4*10^9, instead of 2*10^9... But actually `SERIAL` values are just a subset of `INTEGER` values. That makes sense now, thank you! – johndodo Jan 01 '18 at 21:37
  • @johndodo: no, it's not a "subset". It is **exactly** the same thing as an `integer`. Look at the table definition using `\d` in `psql` and you will see –  Jan 01 '18 at 21:45
  • Agreed, _serial_ is not a subset of _integer_ - but serial _values_ (1 to 2147483647) **are** a subset of integer _values_ (-2147483648 to 2147483647). If I'm not using the precise mathematical terminology here, then sorry about that - english is not my primary language. But I understand exactly what you're saying, it's just that my mistake was elsewhere and your answer helped me identify it. So thanks! – johndodo Jan 01 '18 at 22:05
  • 1
    As I have already written: serial (=sequence) values _can_ start with -2147483648 if you want to. –  Jan 01 '18 at 22:11