Which data type to use to reference SERIAL data type in PostgreSQL?

Question

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?

There's an extension to handle unsigned integers: github.com/petere/pguint - it's referenced from the question you linked. — Aleks G
– Aleks G, Commented Jan 1, 2018 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
– johndodo, Commented Jan 1, 2018 at 21:43

user330315 · Accepted Answer · 2018-01-01 21:25:35Z

20

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)

answered Jan 1, 2018 at 21:25

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

johndodo Over a year ago

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!

user330315 Over a year ago

@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

johndodo Over a year ago

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!

user330315 Over a year ago

As I have already written: serial (=sequence) values can start with -2147483648 if you want to.

Collectives™ on Stack Overflow

Which data type to use to reference SERIAL data type in PostgreSQL?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related