Serial Numbers In PostgreSQL Explained
This post is part of a series on the numeric data types found in PostgreSQL. Read on to learn all about defining arbitrary precision numbers in PostgreSQL. Write a comment if you have any questions or corrections!
|smallserial||2 bytes||1 - 32,767|
|serial||4 bytes||1 - 2,147,483,647|
|bigserial||8 bytes||1 - 9,223,372,036,854,775,807|
serial data type is PostgreSQL's way of specifying an autoincrementing numeric column. In this sense, it is not a true type. It's pretty convenient though. Specifying:
CREATE TABLE books ( id SERIAL );
is equivalent to this:
CREATE SEQUENCE books_id_seq; CREATE TABLE books ( id INTEGER NOT NULL DEFAULT nextval('books_id_seq') ); ALTER SEQUENCE books_id_seq OWNED BY books.id;
Definitely the first option is much less verbose! Typically you see the
serial type used with the
PRIMARY KEY constraints. Also, you'll notice that a
serial data type has the
NOT NULL constraint applied to it automatically, to ensure that a null value can't be inserted.
serial types are equivalent to the
integer types as far as their positive range goes.
- smallserial (range: 1 - 32,767) = positive smallint range (1 - 32,767)
- serial = int
- bigserial = bigint
More Numeric Types
Learn about the other numeric data types in one of the following posts:
What to see more of this kind of content? Let me know in the comments or reach out on Twitter!