CODEWITHSUNDEEP
postgresql
johndodo
johndodo

Reputation: 18261

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

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?

Upvotes: 13

Views: 7209

Answers (1)

user330315
user330315

Reputation:

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)

Upvotes: 18

Related Questions